24 Hours Of Pass 2010

Check the link to obtain information and register for 24 hours of Pass 2010. This is 24 hours of back to back SQL Server related webcasts starting on May 19 at 12:00 GMT(UTC). There are a lot of good presentations available go ahead, check it out and register now. I don't think I can watch all but I hope I will have time for most.

MSSQL Replication 'Field Size Too Large'

Back from a vacation and I am ready to blog at least once a week. Last week there was an update to a large table which was involved in transactional replication. We wanted to move from using GUID as primary key and implement integer based identity key. So I had to stop publishing the table to be able to drop the primary key and add a new column, populate it create the primary key on the new column.

So we had the script executed to do the job. The I used the same script which alters the table to make the necessary changes in the subscriber database. Because the table in subscriber was in different file group with different indexes and I did not want to the replication drop and recreate the table itself (which is default). I then initiated the replication with a new snapshot and waited for 5 minutes. Then I saw the error message and found out that it's complaining about the field size too larger. How can that be? Both tables are exactly same for the basic schema. I tried to create a new snapshot with the same ugly error message. Then I decided to script out my additional indexes in subscriber and dropped the table. I created the table in subscriber from a script created in the publisher with my additional indexes in the specific file group I want. The I set the publication to create a new snapshot with leaving the target table in subscriber left as it is. And that did it.

Hope this helps.

SQL Server 2005 and 2008 Service Pack Announcements

Microsoft announced the upcoming targeted release dates for SQL Server 2005 and 2008 Service Packs.

SQL Server 2005 Service Pack 4 targeted release date is Q42010.
SQL Server 2008 Service Pack 2 targeted release date is Q32010.

For most people who has been waiting for SQL 2005 SP4 maybe over before the end of the year.

Suspect_Pages

I think not many database administrators are aware of the msdb.dbo.suspect_pages table in SQL Server 2005/2008. The database engine uses this table to record possible suspect page data. It records the pageid and event_type_value which indicates what type of error or how the page repaired along with some other data. For detecting suspect page in your server I recommend setting up a job which looks at that table with certain event_type_value. (the values can be 1,2,3,4,5,7) If database engine records a row with value less than equal to 3 then it means you're facing possible CRC failure, checksum error, or torn page. Then you can send an email alert to your self to start looking for solution. You can use DBCC commands to repair the page or if it does nt work you may have to restore the page from a backup. Suspect_pages table is like having some early warning system so I think dba's should use this table to alert themselves. You can read more about it here on MSDN Site.

HTH

SQL Server 2008 System Views Map

Microsoft has published SQL Server 2008 System View map in .pdf and .xps file format. It's a must for SQL Server DBA. Get the map from here.

Cannot drop the database 'MyDatabase' because it is being used for replication. [SQLSTATE 42000] (Error 3724)

I wanted to drop and restore a database which I restored couple days ago in a test environment. The same db in production is the publisher and there are objects in transactional replication. I was asked to restore the new backup. However drop database returned the error message in the subject line and did not drop the db. Little bit of searching and I found out that the stored procedure can be used to remove the replication prior to dropping the database in test environment. So I connected to my test server and executed following statements in the order.

1. USE MASTER EXEC dbo.sp_removedbreplication @dbname = 'MyDatabase'
2. USE MASTER DROP DATABASE MyDatabase

It worked and I was able to drop and restore the latest backup to the test environment.

Do not run this in production since it will remove the replication from your production publisher.

HTH,

SQL Server SA Login Disabled and Can Not Login To Server

I needed to fix the login issue on newly installed SQL Server 2008 developer edition for a developer who is just starting to work. The tech who prepared the workstations forgot to add the local admin and the AD user account and he was out of the office. So even with the local administrator account no one can log in to SQL Server. Thanks to new security settings of SQL Server 2008. I did a little research and found out the solution. Here is the list of actions for the solution. First open the SQL Server Configuration Manager.

1. Stop SQL Server Service.
2. Double click on SQL Server service and goto advanced tab.
3. add -m; to the beginning of Startup Parameters
4. Start SQL Server Service
5. Open command prompt and connect to your the server using SQLCMD
6. Create a log in (CREATE LOGIN BillGates WITH PASSWORD = 'password')
7. Now add the log in to sysadmin group (EXEC sp_addsrvrolemember 'BillGates','sysadmin')
8. Remember you need to issue GO command to execute the commands.
9. Once you created the login and add it to sysadmin role go to SQL Server Configuration Manager.
10. Stop SQL Server service.
11. Remove -m; from startup parameters.
12. Start SQL Server Service and login with the user that you just created.
13. Now you have sysadmin rights.

HTH,

Bulent