Today got a task to create a common account across all SQL servers which have read privileges on master and msdb database, so to carry out this activity, wrote a small script as follows for SQL 2000 and SQL 2005/2008, Note: Replace test with your account name and password as your desired password Script for SQL 2005/2008, SET NOCOUNT ON USE MASTER CREATE LOGIN [test] WITH PASSWORD=N’test@123′, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE MASTER CREATE […]

While searching for t-sql script which can check replication agent status came across a good script which works well for all versions of SQL Server i.e. 2000/2005/2008/2008R2 and above…

Today users reported unique accessibility issues where all SQL logins are found locked out in all SQL servers (2005, 2008) where Windows enforce password policy is enabled, on further investigation it was found for each SQL login there are more than 20 failed attempts in SQL server error log. It took lot of effort in finding a root cause as on first attept it looked like a SQl server virus injection, but later found that […]

I was going through some posts related to SQL Server capacity and came across this link – http://technet.microsoft.com/en-us/library/ms143432(SQL.100).aspx. Its amazing to see the max capacity specification for SQL Server. These are for SQL Server 2008, but they haven’t changed from SQL Server 2005’s limit. I would love to see systems with near capacity in below specifications: SQL Server Database Engine object Maximum sizes/numbers SQL Server (32-bit) Maximum sizes/numbers SQL Server (64-bit) Database size 1,048,516 terabytes […]