DallasDBAs.com

Explaining SQL Server in plain english

Header Right

Blog

by Leave a Comment

Database in Read-Only after detach/attach

I got a “middle of the night panic call” this morning. My customer was moving 2 user databases to a new drive on the same server.

After detach and copy, he re-attached successfully. But, the databases are in read-only mode and cannot be changed (Error 5105, Device activation error. The physical file name ‘%.*ls’ may be incorrect.)

SQL 2000, post Sp3, Windows 2003, sp1.

Additional errors:
2007-01-16 03:06:41.02 spid61 Starting up database ‘MyDatabase’.
2007-01-16 03:06:41.02 spid61 udopen: Operating system error 5(Access is denied.) during the creation/opening of physical device F:SQLDataMyDatabase_Data.mdf.
2007-01-16 03:06:41.02 spid61 FCB::Open failed: Could not open device F:SQLDataMyDatbase_Data.mdf for virtual device number (VDN) 1.
2007-01-16 03:06:41.02 spid61 udopen: Operating system error 5(Access is denied.) during the creation/opening of physical device F:SQLDataMyDatabase_Log.ldf.
2007-01-16 03:06:41.02 spid61 FCB::Open failed: Could not open device F:SQLDataMyDatabase_Log.ldf for virtual device number (VDN) 2.

Turned out that the SQL Server startup account did not have the correct security permissions on the folder or the files. Whoops. 🙂

Kevin3NF

https://dallasdbas.com/324-2/

New Year’s resolutions kill Log Shipping!

by Leave a Comment

New Year’s resolutions kill Log Shipping!

ok…funniest issue I’ve run across in a while 🙂

We have some custom log shipping script that was working just fine until the end of 2006. Starting around January 2, the restore process couldn’t restore fast enough to keep the standby server current.

I looked at the T-Log files for the last 2 weeks and saw that the T-log backups between 3 and 5 am were 2 to 3 times the size they used to be.

Why? Because the customer here is a national Fitness center business that had a huge influx of memberships and activities 🙂

People trying to lose weight broke log shipping.

The fix? Run the restore process more often (it was only running off hours).

You get fries AND a low-fat shake with this one…

Kevin3NF

by Leave a Comment

Autogrow continued:

re: 12/19/2006 SQL 2005 Autogrow issue

I have confirmed with Microsoft that the bug will be fixed in SP2, and that there is no workaround other than monitoring.

I have written a script for my customer that will check the ‘growth’ and ‘status’ values in the sysfiles table of each database on their system. If the growth number is over x and the status is over ‘y’…we have a problem.

The best way to avoid this whole mess:

MANAGE YOUR DATAFILES!!

If you rely on Autogrow, you are not properly administering your database. Autogrow is a failsafe mechanism for when you get unexpected growth, and nothing more.

Kevin3NF

https://dallasdbas.com/326-2/