How to force a database detach?

Hi.
I need to force the detaching of a database, ie. make the detach not to fail
due to active connections. I need the active connections to be broken.
What I really need is to remove the LDF, and since MSSQL lokcs it I need
either to stop the SQL Server or to detach the database from it
momentaniously.
Thanks in advance,
RODOLFO"RODOLFO" <RODOLFO@.discussions.microsoft.com> wrote in message
news:4558BA4E-B20E-451D-AFE2-6F4D7CE98A3A@.microsoft.com...
> Hi.
> I need to force the detaching of a database, ie. make the detach not to
fail
> due to active connections. I need the active connections to be broken.
> What I really need is to remove the LDF, and since MSSQL lokcs it I need
> either to stop the SQL Server or to detach the database from it
> momentaniously.
> Thanks in advance,
> RODOLFO
This may help. I don't remember if I wrote this, or copied it from someone
else. If I did copy it from someone else, thank you.
Essentially, it is a stored proc (needs some work to ensure it doesn't try
to kill it's own process), that will go through each database and kill
connections.
You could then do something like the following in your code:
EXEC sp_KillProcess 'DatabaseToBeDetached'
EXEC sp_detach_db 'DatabaseToBeDetached'
etc.
HTH
Rick
USE master
GO
CREATE PROC dbo.sp_KillProcess
@.dbname nvarchar(128) = ''
AS
-- This will kill all spid's associated with a particular database.
-- If no database name is given, then all non-system databases will
-- be assumed.
BEGIN
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
DECLARE @.KillSpid int,
@.Count int,
@.SQL varchar(500)
IF (@.dbname IS NULL) OR (DATALENGTH(@.dbname) = 0)
-- Assume that we want all connections dropped.
BEGIN
DECLARE KillCursor CURSOR FOR
SELECT a.spid
FROM master.dbo.sysprocesses a, master.dbo.sysdatabases b
WHERE a.dbid = b.dbid
AND b.[name] NOT IN ('master', 'model', 'msdb', 'tempdb')
END
ELSE
-- We only want connections to this specific database to be dropped.
BEGIN
DECLARE KillCursor CURSOR FOR
SELECT a.spid
FROM master.dbo.sysprocesses a, master.dbo.sysdatabases b
WHERE a.dbid = b.dbid
AND b.[name] NOT IN ('master', 'model', 'msdb', 'tempdb')
AND UPPER(b.[name]) = UPPER(@.dbname)
END
OPEN KillCursor
FETCH NEXT FROM KillCursor INTO @.KillSpid
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SET @.SQL = 'KILL ' + CONVERT(varchar, @.KillSpid)
EXEC(@.SQL)
FETCH NEXT FROM KillCursor INTO @.KillSpid
END
CLOSE KillCursor
DEALLOCATE KillCursor
END
GO|||Hi
Take the DB offline.
ALTER DATABASE pubs
SET OFFLINE
WITH ROLLBACK IMMEDIATE
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"RODOLFO" <RODOLFO@.discussions.microsoft.com> wrote in message
news:4558BA4E-B20E-451D-AFE2-6F4D7CE98A3A@.microsoft.com...
> Hi.
> I need to force the detaching of a database, ie. make the detach not to
> fail
> due to active connections. I need the active connections to be broken.
> What I really need is to remove the LDF, and since MSSQL lokcs it I need
> either to stop the SQL Server or to detach the database from it
> momentaniously.
> Thanks in advance,
> RODOLFO|||Hi and thanks... I tried the script but it gives me an error: Can't use KILL
to kill your own process... I'm trying to solve it now, but Im not a SQL
programmer.. any help would be very appreciated :-D
Thanks again,
RODOLFO
"Rick Sawtell" wrote:
> "RODOLFO" <RODOLFO@.discussions.microsoft.com> wrote in message
> news:4558BA4E-B20E-451D-AFE2-6F4D7CE98A3A@.microsoft.com...
> > Hi.
> > I need to force the detaching of a database, ie. make the detach not to
> fail
> > due to active connections. I need the active connections to be broken.
> >
> > What I really need is to remove the LDF, and since MSSQL lokcs it I need
> > either to stop the SQL Server or to detach the database from it
> > momentaniously.
> >
> > Thanks in advance,
> > RODOLFO
> This may help. I don't remember if I wrote this, or copied it from someone
> else. If I did copy it from someone else, thank you.
> Essentially, it is a stored proc (needs some work to ensure it doesn't try
> to kill it's own process), that will go through each database and kill
> connections.
> You could then do something like the following in your code:
> EXEC sp_KillProcess 'DatabaseToBeDetached'
> EXEC sp_detach_db 'DatabaseToBeDetached'
> etc.
>
> HTH
> Rick
>
> USE master
> GO
>
> CREATE PROC dbo.sp_KillProcess
> @.dbname nvarchar(128) = ''
> AS
> -- This will kill all spid's associated with a particular database.
> -- If no database name is given, then all non-system databases will
> -- be assumed.
> BEGIN
> SET NOCOUNT ON
> SET QUOTED_IDENTIFIER OFF
> DECLARE @.KillSpid int,
> @.Count int,
> @.SQL varchar(500)
>
> IF (@.dbname IS NULL) OR (DATALENGTH(@.dbname) = 0)
> -- Assume that we want all connections dropped.
> BEGIN
> DECLARE KillCursor CURSOR FOR
> SELECT a.spid
> FROM master.dbo.sysprocesses a, master.dbo.sysdatabases b
> WHERE a.dbid = b.dbid
> AND b.[name] NOT IN ('master', 'model', 'msdb', 'tempdb')
> END
> ELSE
> -- We only want connections to this specific database to be dropped.
> BEGIN
> DECLARE KillCursor CURSOR FOR
> SELECT a.spid
> FROM master.dbo.sysprocesses a, master.dbo.sysdatabases b
> WHERE a.dbid = b.dbid
> AND b.[name] NOT IN ('master', 'model', 'msdb', 'tempdb')
> AND UPPER(b.[name]) = UPPER(@.dbname)
> END
> OPEN KillCursor
> FETCH NEXT FROM KillCursor INTO @.KillSpid
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> SET @.SQL = 'KILL ' + CONVERT(varchar, @.KillSpid)
> EXEC(@.SQL)
> FETCH NEXT FROM KillCursor INTO @.KillSpid
> END
> CLOSE KillCursor
> DEALLOCATE KillCursor
> END
> GO
>
>
>|||Hi and thanks a lot.
I tried it and I get this error: ALTER DATABASE statement failed. User must
be in the master database.
Im using the sa user... I also tried with a different user with almost every
combination of permissions I could imagine.
Any more ideas?
Thanks a lot.
RODOLFO
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Take the DB offline.
> ALTER DATABASE pubs
> SET OFFLINE
> WITH ROLLBACK IMMEDIATE
>
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "RODOLFO" <RODOLFO@.discussions.microsoft.com> wrote in message
> news:4558BA4E-B20E-451D-AFE2-6F4D7CE98A3A@.microsoft.com...
> > Hi.
> > I need to force the detaching of a database, ie. make the detach not to
> > fail
> > due to active connections. I need the active connections to be broken.
> >
> > What I really need is to remove the LDF, and since MSSQL lokcs it I need
> > either to stop the SQL Server or to detach the database from it
> > momentaniously.
> >
> > Thanks in advance,
> > RODOLFO
>
>|||Hi,
Try this:-
USE Master
GO
ALTER DATABASE <DBNAME> SET OFFLINE WITH ROLLBACK IMMEDIATE
After this detach the database using
sp_detach_db <DBNAME>
Thanks
Hari
SQL Server MVP
>
"RODOLFO" <RODOLFO@.discussions.microsoft.com> wrote in message
news:999DBD22-91B1-41E6-BC04-652DB8B6157A@.microsoft.com...
> Hi and thanks a lot.
> I tried it and I get this error: ALTER DATABASE statement failed. User
> must
> be in the master database.
> Im using the sa user... I also tried with a different user with almost
> every
> combination of permissions I could imagine.
> Any more ideas?
> Thanks a lot.
> RODOLFO
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Take the DB offline.
>> ALTER DATABASE pubs
>> SET OFFLINE
>> WITH ROLLBACK IMMEDIATE
>>
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "RODOLFO" <RODOLFO@.discussions.microsoft.com> wrote in message
>> news:4558BA4E-B20E-451D-AFE2-6F4D7CE98A3A@.microsoft.com...
>> > Hi.
>> > I need to force the detaching of a database, ie. make the detach not to
>> > fail
>> > due to active connections. I need the active connections to be broken.
>> >
>> > What I really need is to remove the LDF, and since MSSQL lokcs it I
>> > need
>> > either to stop the SQL Server or to detach the database from it
>> > momentaniously.
>> >
>> > Thanks in advance,
>> > RODOLFO
>>

How to find which tables allocates disk space.

I just had truncate a table in a database that had grown too big, due to fawlty log settings. Now the database only hold 20 MB of data but the allocated size of the database is still 4 GB.
Is there any way I can find which one of the tables is responsible for allocating that amount of disk space? I haven't found any view that shows this. Nor did I find any interface to show any size properties regarding the system tables, like the "Task Pad" do for tables created by users.
please help me out here.
kind regards
sven
**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...Sven
To reduce a physical size of the database perform DBCC SHRINKFILE command
Look at sp_spaceused stored procedure in the BOL.
"sven ekdahl" <sveek328@.student.liu.se> wrote in message
news:%23vS5wSZxEHA.3108@.TK2MSFTNGP14.phx.gbl...
> I just had truncate a table in a database that had grown too big, due to
fawlty log settings. Now the database only hold 20 MB of data but the
allocated size of the database is still 4 GB.
> Is there any way I can find which one of the tables is responsible for
allocating that amount of disk space? I haven't found any view that shows
this. Nor did I find any interface to show any size properties regarding the
system tables, like the "Task Pad" do for tables created by users.
> please help me out here.
> kind regards
> sven
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...|||Hi
Have a look at DBCC SHRINKDATABASE in BOL.
Regards
Mike
"sven ekdahl" wrote:
> I just had truncate a table in a database that had grown too big, due to fawlty log settings. Now the database only hold 20 MB of data but the allocated size of the database is still 4 GB.
> Is there any way I can find which one of the tables is responsible for allocating that amount of disk space? I haven't found any view that shows this. Nor did I find any interface to show any size properties regarding the system tables, like the "Task Pad" do for tables created by users.
> please help me out here.
> kind regards
> sven
> **********************************************************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
>|||Mike
By using DBCC SHRINKDATABASE you cannot reduce a size of the database
smaller than this minimum size.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:A4C1B7E7-8C03-44C9-9BCD-406758D7937E@.microsoft.com...
> Hi
> Have a look at DBCC SHRINKDATABASE in BOL.
> Regards
> Mike
> "sven ekdahl" wrote:
> > I just had truncate a table in a database that had grown too big, due to
fawlty log settings. Now the database only hold 20 MB of data but the
allocated size of the database is still 4 GB.
> >
> > Is there any way I can find which one of the tables is responsible for
allocating that amount of disk space? I haven't found any view that shows
this. Nor did I find any interface to show any size properties regarding the
system tables, like the "Task Pad" do for tables created by users.
> >
> > please help me out here.
> >
> > kind regards
> > sven
> >
> > **********************************************************************
> > Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> > Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...
> >|||**********************************************************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

How to find which tables allocates disk space.

I just had truncate a table in a database that had grown too big, due to fawlty log settings. Now the database only hold 20 MB of data but the allocated size of the database is still 4 GB.
Is there any way I can find which one of the tables is responsible for allocating that amount of disk space? I haven't found any view that shows this. Nor did I find any interface to show any size properties regarding the system tables, like the "Task Pad
" do for tables created by users.
please help me out here.
kind regards
sven
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Sven
To reduce a physical size of the database perform DBCC SHRINKFILE command
Look at sp_spaceused stored procedure in the BOL.
"sven ekdahl" <sveek328@.student.liu.se> wrote in message
news:%23vS5wSZxEHA.3108@.TK2MSFTNGP14.phx.gbl...
> I just had truncate a table in a database that had grown too big, due to
fawlty log settings. Now the database only hold 20 MB of data but the
allocated size of the database is still 4 GB.
> Is there any way I can find which one of the tables is responsible for
allocating that amount of disk space? I haven't found any view that shows
this. Nor did I find any interface to show any size properties regarding the
system tables, like the "Task Pad" do for tables created by users.
> please help me out here.
> kind regards
> sven
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...
|||Hi
Have a look at DBCC SHRINKDATABASE in BOL.
Regards
Mike
"sven ekdahl" wrote:

> I just had truncate a table in a database that had grown too big, due to fawlty log settings. Now the database only hold 20 MB of data but the allocated size of the database is still 4 GB.
> Is there any way I can find which one of the tables is responsible for allocating that amount of disk space? I haven't found any view that shows this. Nor did I find any interface to show any size properties regarding the system tables, like the "Task P
ad" do for tables created by users.
> please help me out here.
> kind regards
> sven
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
>
|||Mike
By using DBCC SHRINKDATABASE you cannot reduce a size of the database
smaller than this minimum size.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:A4C1B7E7-8C03-44C9-9BCD-406758D7937E@.microsoft.com...[vbcol=seagreen]
> Hi
> Have a look at DBCC SHRINKDATABASE in BOL.
> Regards
> Mike
> "sven ekdahl" wrote:
fawlty log settings. Now the database only hold 20 MB of data but the
allocated size of the database is still 4 GB.[vbcol=seagreen]
allocating that amount of disk space? I haven't found any view that shows
this. Nor did I find any interface to show any size properties regarding the
system tables, like the "Task Pad" do for tables created by users.[vbcol=seagreen]
ASP.NET resources...[vbcol=seagreen]
|||how do I call this SHRINKFILE procedure? What's meant with the logical file name of a database?
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||To find the logical file names you can use : sp_helpdb <dbanme>
"sven ekdahl" <sveek328@.student.liu.se> wrote in message
news:OIxjYiaxEHA.908@.TK2MSFTNGP11.phx.gbl...
> how do I call this SHRINKFILE procedure? What's meant with the logical
> file name of a database?
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
> ASP.NET resources...
|||************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

How to find which tables allocates disk space.

I just had truncate a table in a database that had grown too big, due to faw
lty log settings. Now the database only hold 20 MB of data but the allocated
size of the database is still 4 GB.
Is there any way I can find which one of the tables is responsible for alloc
ating that amount of disk space? I haven't found any view that shows this. N
or did I find any interface to show any size properties regarding the system
tables, like the "Task Pad
" do for tables created by users.
please help me out here.
kind regards
sven
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...Sven
To reduce a physical size of the database perform DBCC SHRINKFILE command
Look at sp_spaceused stored procedure in the BOL.
"sven ekdahl" <sveek328@.student.liu.se> wrote in message
news:%23vS5wSZxEHA.3108@.TK2MSFTNGP14.phx.gbl...
> I just had truncate a table in a database that had grown too big, due to
fawlty log settings. Now the database only hold 20 MB of data but the
allocated size of the database is still 4 GB.
> Is there any way I can find which one of the tables is responsible for
allocating that amount of disk space? I haven't found any view that shows
this. Nor did I find any interface to show any size properties regarding the
system tables, like the "Task Pad" do for tables created by users.
> please help me out here.
> kind regards
> sven
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...|||Hi
Have a look at DBCC SHRINKDATABASE in BOL.
Regards
Mike
"sven ekdahl" wrote:

> I just had truncate a table in a database that had grown too big, due to f
awlty log settings. Now the database only hold 20 MB of data but the allocat
ed size of the database is still 4 GB.
> Is there any way I can find which one of the tables is responsible for allocating
that amount of disk space? I haven't found any view that shows this. Nor did I find
any interface to show any size properties regarding the system tables, like the "Tas
k P
ad" do for tables created by users.
> please help me out here.
> kind regards
> sven
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NE
T resources...
>|||Mike
By using DBCC SHRINKDATABASE you cannot reduce a size of the database
smaller than this minimum size.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:A4C1B7E7-8C03-44C9-9BCD-406758D7937E@.microsoft.com...[vbcol=seagreen]
> Hi
> Have a look at DBCC SHRINKDATABASE in BOL.
> Regards
> Mike
> "sven ekdahl" wrote:
>
fawlty log settings. Now the database only hold 20 MB of data but the
allocated size of the database is still 4 GB.[vbcol=seagreen]
allocating that amount of disk space? I haven't found any view that shows
this. Nor did I find any interface to show any size properties regarding the
system tables, like the "Task Pad" do for tables created by users.[vbcol=seagreen]
ASP.NET resources...[vbcol=seagreen]|||how do I call this SHRINKFILE procedure? What's meant with the logical file
name of a database?
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||To find the logical file names you can use : sp_helpdb <dbanme>
"sven ekdahl" <sveek328@.student.liu.se> wrote in message
news:OIxjYiaxEHA.908@.TK2MSFTNGP11.phx.gbl...
> how do I call this SHRINKFILE procedure? What's meant with the logical
> file name of a database?
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
> ASP.NET resources...|||****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...