If you want to drop all the connections on you sql server that are accessing a particular database, here's some code:
USE master
go
DECLARE @dbname sysname
SET @dbname = 'name of database you want to drop connections from'
DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE ('KILL ' + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END
Another method you could use would be to take the database offline for a moment:
USE master
GO
ALTER DATABASE dbname
SET OFFLINE WITH ROLLBACK IMMEDIATE
ALTER DATABASE dbname
SET ONLINE
I've been asked plenty of times, how do you reset an IDENTITY column? After inserting data into
a table, and then deleting everything, how do I make the IDENTITY to start from 1 again?
Personally I think is a bad idea to reset identity columns to any value lower than they
currently are. To find out why, read on. But first, let me show you two ways you can reset an
identity column.
Reseeding an IDENTITY column to a new value
-- syntax
DBCC CHECKIDENT ('mytablename', RESEED, new identity value)
-- example
DBCC CHECKIDENT ('Customer', RESEED, 1000)
Resetting IDENTITY to the next highest number not in use
-- syntax
DBCC CHECKIDENT ('mytablename')
-- example
DBCC CHECKIDENT ('Customer')
Now why would it be a bad idea to reset an Identity column to a lower number? To say change it
from 5,000 to 1? Because if your table is not empty, you might create a duplicate. If you have a
unique index, you may recieve an error when inserting. And reusing numbers can cause confusion for
users/developers.
Identity columns are typically used as unique references. Order Numbers, Customer Number,
Receipt Number, Invoice Number, Patient Number, etc. Things where you don't want to confuse two
different entities. Used as surrogate keys with referential integrity they are quite handy.
Reusing a number means they no longer represent unique things.
But if you're using a unique index, your safe right? Not entirely. Say you only have 100 rows
in your table, identities 5,000 to 5,100. For some reason they start at 5,000, and you decide to
reset to 1 without truncating the table, because it looks nice. What happens after 4,999 inserts?
Identity is set to 5,000. But you already have an 5,000 record. So you next insert will fail. As
will all other insert attempts until IDENTITY reaches a number that is not already in use. If
those were sales that you missed out on, that could be a serious loss.
What about in your development environment? I still think you should not reset them there
either. Can all your applications handle high numbers? If you've declared the identity as an INT,
but have used it as a smallint somewhere else, you might never realise until you hit a number
smallint doesn't handle. What about testing procedures? Are they expecting certain low values to
always exist? What about reporting? Can that handle numbers being reused, or does it change
historical reports?
There is one instance where I DO support resetting identity, but only using the DBCC CHECKIDENT ('mytablename') method: after making inserts with
IDENTITY_INSERT ON. In this case you ARE very likely to create duplicate numbers / insert errors.
Immediately after making inserts with SET IDENTITY_INSERT ON, you should reset the identity. Don't
specify a new value and the DBCC CHECKIDENT command will reset it to the maximum current value of
the identity column. This way the next insert will be a unique number.
But please, don't reset IDENTITY to a number lower than it's current value. You really don't
gain anything by having identity columns that start at 1. It doesn't use any less resources to
start at 1 than it does to use 1,000,000.
I was asked today how to select distinct columns while ordering by another column that does not
appear in the column output list. For example:
select distinct [name]
from master..syscolumns
order by xtype
If you try to run this code, you will get the error message:
Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
One way to get around this is to place the ORDER BY in a derived table, used as the source for
the DISTINCT query. eg:
select distinct [name] from (
select [name]
from master..syscolumns order by xtype ) as dt
However you will get this error:
Server: Msg 1033, Level 15, State 1, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless
TOP is also specified.
But what if you don't want to limit your results to just the X TOP rows? What if you
want all the rows?
Well not only can TOP be used to limit the number of rows a query returns, it can be used to
instruct (some what redundantly) that you want all the rows: TOP 100 PERCENT.
select distinct [name]
from (
select top 100 percent [name]
from master..syscolumns order by xtype
) as dt
And there you have it! Distinct results ordered by a column that is not output!
Here's the SHOWPLAN_TEXT that shows the sort takes place:
|--Hash Match(Aggregate, HASH:([syscolumns].[name]), RESIDUAL:([syscolumns].[name]=[syscolumns].[name]))
|--Sort(ORDER BY:([syscolumns].[xtype] ASC))
|--Clustered Index Scan(OBJECT:([master].[dbo].[syscolumns].[syscolumns]))
Over the holiday break one of our servers suffered a disk failure in the main raid set.
The disk was replaced and the raid set repaired. All seemed to be going well,
until SQL Server wouldn't start. Checking the latest ERRORLOG for SQL Server
showed:
and nothing more. Looking up the error numbers in BOL shows:
Error 823 - Microsoft® SQL Server™ encountered an I/O error on a read or write request made to a device. This error usually indicates disk problems. However, additional kernel messages in the error log, recorded before error 823, should indicate which device is involved.
Error 5173 - Cannot associate files with different databases.
Error 5180 - Could not open FCB for invalid file ID %d in database '%.*ls'.
With a master database this corrupted, SQL Server will not start. So the first
task is to rebuild the master database, and then restore the orginal from a
backup before the disk corruption occured. Once SQL Server is working again
I'll check all the other databases for corruption.
Rebuilding the master database is done using the Rebuild Master utility
"rebuildm.exe" which is located in the 80\Tools\Binn directory of the
SQL Server installation.
Select the server and instance that you want to rebuild.
Now you are going to need access to the files from the install CD for SQL Server.
Browse to the Data dircetory in the SQL Server install files.
Set the collation you want SQL Server to be using, and hit Rebuild.
If you are run the utility using the CD as the source, you might encounter the error:
Rebuild Master failed with error -1:
Microsoft Support Q273572 lists the error as a bug in the utility. As it copies the read-only
files from the install CD it does not turn off the read-only flag. To work around this
simply copy the Data directory from the CD to the harddrive and turn off the read-only
setting for the files. Then go ahead and rebuild again.
With the master database rebuilt, verify that it worked by starting SQL Server. The instance should
start, but all the previous settings and databases will be missing. Restoring the Master database
will bring back all the previous instance settings.
Stop the SQL Server instance. And start it in single user mode. Open a command prompt window.
Change into you SQL Server Binn directory for the instance you are repairing (the default is c:\Program Files\Microsoft SQL Server\MSSQL\Binn) and run:
sqlservr.exe -c -m
The -c option tells SQL Server to run independantly, and not as a service.
The -m option tells SQL Server to run in single-user mode.
Your sql server instance will then start up and you can connect to it with Query Analzyer.
If you get the error message:
Server: Msg 18461, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failer for user 'x'.
Reason: Server is in single user mode. Only one administrator can connect at this time.
then you need to make sure that no other administrators are accessing the server. If you have
Enterprise Manager or the SQL Server Service Manager open, close them, and try again. Make sure
that any other services (like SQL Agent) that access SQL Server are also stopped.
Once you have successfully connected to your SQL Server instance in Query Analyzer, restoring
the master database simply requires running the RESTORE DATABASE command:
RESTORE DATABASE master FROM DISK='N:\Backups\master.bak'
SQL Server should return:
The master database has been successfully restored. Shutting down SQL Server.
SQL Server is terminating this process.
Once your master database is restored, SQL Server will reboot and attempt
recovery on the other databases listed in your master database. Check the
error log again to see what databases have SQL Server is unable to recover.
2005-12-28 16:16:00.66 spid11 Starting up database 'HHH'.
2005-12-28 16:16:27.85 spid11 Error: 9003, Severity: 20, State: 1
2005-12-28 16:16:27.85 spid11 The LSN (72984:107911:2) passed to log scan in database 'HHH' is invalid..
2005-12-28 16:16:28.07 spid11 Error: 3414, Severity: 21, State: 1
2005-12-28 16:16:28.07 spid11 Database 'HHH' (database ID 7) could not recover. Contact Technical Support..
During the recovery of this server I found four databases were unrecoverable. Again, restoring these databases
from their latest backup resolved the problem.
To ensure that all the databases were infact ok, I ran DBCC CHECKDB. A quick way to run it on
all databases is with this script:
Using the CONVERT( ... , ... , style ) function we can specify the style attribute, that lets us specify to SQL Server (using style 14 or 114) that we just want the time portion.
-- Get current time (without date) in 24-hour time:
SELECT CONVERT( CHAR(8), GetDate(), 14)
By changing the number of CHAR characters you can select the time with or without milliseconds and seconds.
-- Get current time (without date) in HH:MM:SS:mmm format:
SELECT CONVERT( CHAR(12), GetDate(), 14)
-- Get current time (without date) in HH:MM:SS format:
SELECT CONVERT( CHAR(8), GetDate(), 14)
-- Get current time (without date) in HH:MM format:
SELECT CONVERT( CHAR(6), GetDate(), 14)
Q: When I use the condition LEN(columnname) = 0, I don't get columns where columnname value is null. Why? Shouldn't LEN(NULL) = 0 ? Are my ANSI_NULLS or ANSI_DEFAULTS setting to blame?
A: No. LEN(NULL) will always return NULL. From SQL Server Books Online Null Values
A value of NULL indicates the value is unknown. A value of NULL is different from an empty or zero value. No two null values are equal. Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown. ...
When null values are present in data, logical and comparison operators can potentially return a third result of UNKNOWN instead of just TRUE or FALSE.
Ansi settings don't have any affect on this behaviour. You can check this with:
SET ANSI_DEFAULTS on
SELECT LEN(NULL), LEN('')
SET ANSI_DEFAULTS off
SELECT LEN(NULL), LEN('')
