Failed to Pause Catalog for Backup

Today I was trying to backup some database on one box, but there was some error with only one database. It was Failed to pause catalog for backup, when I was searching for this error then found that Database is using Full Text Search Service, but the problem was only that the Full Text Search Service was not running, so in that case the solution is just to start Full text search service and take backup of those databases.

Insufficient System Resources... When growing database physical file

We had a very severe issue in our production,

On Database:
3.2 TB Size of Partitioned Database with 60 File Groups
Every File Group contains One month data in its only one physical File.
Physical Files are of different size between 10GB and 300GB.

I was using this command to expand the physical file,

ALTER DATABASE [ODS] MODIFY FILE ( NAME = N'ODSFG33File1', SIZE = 307200000KB )

but was getting the following error,

MODIFY FILE encountered operating system error 1450(Insufficient system resources exist to complete the requested service.) while attempting to expand the physical file.

After trying a lot for this, the problem found that the physical file was highly fragmented, and offcourse the it was somehow exceeding the int variable capacity, which seems as created in the backend windows 2003 Server code,

So, Let's move to the solution,

Defrag the physical file using contig.exe
-------OR
Move the physical file to another network location, defrag the drive, and move file to its previous location

Create Index on Partitioned Table

Index must be aligned on Partitioned Tables, In order to create index on Partitioned Table, DBA needs to identify, whether he want to create an index which will unique or it will allow duplicates,

Unique Index:

If the required Index is Unique then partitioning column must be part of index key columns

Duplicate is ok:

If the required Index is with duplicate ok, then no need to include the partitioning column in index key columns, Just specify columns and create index based on Partitioning Scheme

Agent XPs Disabled

Restarting SQL Server sometimes causes Agent XP disabled, so if you try to start then SQL Server Agent it will start and then immediately stop, but it will show you Agent XPs Disabled in brackets with Agent name, to confirm you can also run the following query,

Select Name, Value from sys.Configurations Where name in ('Agent XPs','Show Advanced Options')

If it is showing Agent XPs Value Zero then Use the following code to change it,

exec sys.sp_configure @configname = N'show advanced options', @configvalue = 1
reconfigure with override
exec sys.sp_configure @configname = N'Agent XPs', @configvalue = 1
reconfigure with override
exec sys.sp_configure @configname = N'show advanced options', @configvalue = 0
reconfigure with override

After running the above line, just sit with patience or have a cup of coffee meanwhile because it will automatically starts Agent but will take 10 to 15 minutes.

Database is in Transition

There are some transactions in process and you try to make database offline, then you will have to sit with patience while SQL Server successfully abort/commit all transactions on backend and make it offline successfully meanwhile if you will try to access the database then get the msg, "Database is in transition", if you cannot sit with patience then just kill the thread from Activity Monitor where you see the command alter database set flat offline the other option is to restart the sql server, but in case of restart, it will not give you control of database, after restart the database will go in auto recovery mode, but after some minutes and this successful recovery you will be able to do any operation on database.

Compression on Filegroups

Microsoft SQL Server offers compression on readonly filegroups in just simple steps,

Make the Filegroup Readonly
Make Database Offline
Right click the file, Properties, Advanced, Check Compress Contents.... , Apply, OK
Make Database Online

It really save disk space, with a minimal impact on performance, I tried on small filegroup of 30GB, it works great, and got free space around 2/3 of actual size.
But when tried on big filegroups of size 80GB and more then just got 10% compression, and system went into sleeping state and after waiting for a number of hours, I found on that in this case system can be restarted (only option), because cannot trace or catch sleeping or backend internal system threads, so i just restarted the system and got the control of file and made my production server up and running with safe recovery. Really excellent experience with a downtime of 48 hours, without big gain on storage. So please dont try this on your server if your files are of size larger than 40GB.

Partitioning Table - Sliding window

I have a database of 3 terabytes in production with 4 big partitioned tables of sizes between 500GB to 900GB. I was planning to move one year old data into some temporary database and backup that one to tapes and delete that old data from actual database.

here are the three easy steps to do it,

Step 1: Check for the partition from partition function,

select $partition.MonthlyPFN('2005-May-01')

It will give information, in which partition, data is stored for May 2005, My tables are partitioned on monthly basis.

It has returned, FG4, as initially I created partitions for 5 years in March 2005, to keep data till 2010 in partitions from FG1 to FG60. Monthly Partitioned tables.

Step 2: Create a Table exactly same as of the table, from where you want to delete or move data, in same file group,

CREATE TABLE [ods].[BLA_BLA_MAY_2005](
[ID] [bigint] NOT NULL,
[FILENAME] [varchar](100) NULL,
[DATELOADED] [datetime] NULL,
) ON [FG4]

Step 3: Use Alter table switch statement to move data to temporary table, which you can drop later,

ALTER TABLE SCHEMA.SOURCE_TABLE SWITCH partition SOURCE_PARTITION_NUMBER TO SCHEMA.TARGET_TABLE partition TARGET_PARTITION_NUMBER

e.g., ALTER TABLE ODS.BLA_BLA SWITCH partition 4 TO ODS.BLA_BLA_MAY_2005

for more help on alter table switch, visit: http://msdn2.microsoft.com/en-us/library/ms190273.aspx

Reporting Services Security

Microsoft SQL Server 2000 Reporting Services, we are running on some servers. My team member came to me that Report manager page does nt appear as it should be, there are no options for uploading of files, I start analysis on this and found that ReportServer Database has no issues, but only the problem was anonymous log in in IIS was enabled for virtual directory with some local user, and we have database on another SQL box. So, it appeared as the developer last time uploaded his reports and to enabled anonymous access, he just enabled the virtual directory security. I just unchecked the anonymous access of virtual directory, and restarted IIS and problem get solved. Report Manager in Microsoft SQL Server Reporting Services inherited from Report Server.

about me

I am Mohammed Imran Shaikh, working with Microsoft SQL Server since 1999. I have a good experience and sound knowledge, with almost all areas of Microsoft SQL Server. Nowadays working with Omani Qatari Telecommunications SAOC, Nawras, in Muscat Oman with the team of IT Dept as a DBA /System Analyst. Where I had a chance to develop two Multiterabyte Datawarehouses and Reporting Solutions using, Microsoft SQL Server 2005 technologies and Microsoft dot net (c#, asp.net), with the Administration of 9 SQL Servers in Production, with a lot of activities like Analysis, development, performance tuning, data integration. Enjoying my work with always overloaded work loads.