DENY permission,SQL server: disk

Showing posts with label disk. Show all posts

Thursday, March 29, 2012

Deleting records to save DISK Space...

Hi,

In my data archiving process , I would end up deleting hunders records from the production databases but would that help me save some DISK space immediately? Should I run some DBCC command to get some disk space ?

if SO ..!! What should I do after deleting the records..??

Thanks
Cheriyan."Always be sure to backup your records ... before you delete them ... "|||Have a look in SQL Book online to this command

DBCC SHRINKDATABASE
( database_name [ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)|||Kim Tripp presented a good session at the PASS conference a couple weeks ago about removing data using filegroups. If you are a PASS member, you can download the presentation here:

http://ew.sqlpass.org/ew/pass/callpapers/attach/TRIPP-Rolling_Range_For_Print.zip

Anyone can also download her scripts here:

http://www.sqlskills.com/pastConferences.asp

Tuesday, March 27, 2012

Deleting Older Files

I have a backup job that has failed.

The database size is 20.6 GB and the Transaction logs are 135MB

The amount of disk space I have left is 3.65MB. Which I know is not going to work.

However on the maintenance plan it is suppose to remove files older than 1 day.

I am wondering if a job works like this:

Step 1 create backup file
Step 2 Create Transaction Log Back up
Step 3 Delete old backup file
Step 4 Delete old Transaction log back up

Which tell me I would need to have double amount of disk space to accommodate 2 20 GB backup file and 2 135MB Transaction log file.

Is this correct??

Also is there a way that I can have step 3,4 done first.

LystraAs far as the full backup, you can overwrite the old backup file every time you take a full backup. This way you don't need to reserve the space for two full backup file. In the case of log backup, if you don't need to or don't want to backup the log file, just use simple recovery mode. It looks like you are deleting them any way.|||That's the problem it is not deleting the older files, even though I have it check to delete the files.

The database recovery mode is set to full.

Lystra|||Drives are cheap....

And what happens when you need to migrate data around?

Do you have a disaster box?

How odten do you dump the transaction log?

How many dumps do you have now?

How big is the hard drive?|||I think I have solve the problem. The actual mdf is 47.2GB big and there is not enough disk space to accommodate this backup.

Thanks

Lystra|||Not to sound like a broken record.

I think I have solve the problem. The actual mdf is 47.2GB big and there is not enough disk space to accommodate this backup.

Thanks

Lystra

Wednesday, March 21, 2012

Deleting data with least impact

I have a large database - 33 million rows - VERY WIDE table and am
running out of disk space - lovely ...
I have to move 7 million records off the table - no biggie - I'll just
dts the records to another server.
My problem is deleting the remaining data to make room on the server
without impacting/logging too much.
Any help would be appreciated.
Thanks,
CraigOn Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
> I have a large database - 33 million rows - VERY WIDE table and am
> running out of disk space - lovely ...
> I have to move 7 million records off the table - no biggie - I'll just
> dts the records to another server.
> My problem is deleting the remaining data to make room on the server
> without impacting/logging too much.
> Any help would be appreciated.
> Thanks,
> Craig
truncate|||On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
> I have a large database - 33 million rows - VERY WIDE table and am
> running out of disk space - lovely ...
> I have to move 7 million records off the table - no biggie - I'll just
> dts the records to another server.
> My problem is deleting the remaining data to make room on the server
> without impacting/logging too much.
> Any help would be appreciated.
> Thanks,
> Craig
you can truncate the table one the data is moved out.|||On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
> On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
> > I have a large database - 33 million rows - VERY WIDE table and am
> > running out of disk space - lovely ...
> > I have to move 7 million records off the table - no biggie - I'll just
> > dts the records to another server.
> > My problem is deleting the remaining data to make room on the server
> > without impacting/logging too much.
> > Any help would be appreciated.
> > Thanks,
> > Craig
> you can truncate the table one the data is moved out.
I will only need to delete 7 m rows of the 33 m rows ... so I need a
good way to do that.
Craig|||On Sep 12, 11:27 am, Craig <csomb...@.gmail.com> wrote:
> On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
>
>
> > On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
> > > I have a large database - 33 million rows - VERY WIDE table and am
> > > running out of disk space - lovely ...
> > > I have to move 7 million records off the table - no biggie - I'll just
> > > dts the records to another server.
> > > My problem is deleting the remaining data to make room on the server
> > > without impacting/logging too much.
> > > Any help would be appreciated.
> > > Thanks,
> > > Craig
> > you can truncate the table one the data is moved out.
> I will only need to delete 7 m rows of the 33 m rows ... so I need a
> good way to do that.
> Craig- Hide quoted text -
> - Show quoted text -
I need to look at the delete statement. How long it takes now?|||Do not forget changing your Recovery Model temporarily to "Simple Recovery
Model". And before doing this, backup your Transaction Log, otherwise log
chain will be broken and you will not be able to restore your database to
the point where you changed your recovery model if you encounter a problem.
After your deleting operation, change your Recovery Model back to FULL.
(After changing your recovery model to FULL, back up your transaction log
again to prevent breaking the log chain)
The aim of changing your recovery model is not to log all this 7million
deletion to the transaction log file and blow it up. You change your
recovery model to prevent this. Because in this operation (as you already
lack of free space on your disks) if you keep FULL recovery model, then it
would log all this deletion operation and it probably raise an "out of
space" error and halt the process of deletion or whatever.
Here's a link that you can obtain more info abour Recovery Models from:
http://msdn2.microsoft.com/en-us/library/ms366344.aspx
--
Ekrem Önsoy
"Craig" <csomberg@.gmail.com> wrote in message
news:1189574876.579528.58280@.r29g2000hsg.googlegroups.com...
> On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
>> On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
>> > I have a large database - 33 million rows - VERY WIDE table and am
>> > running out of disk space - lovely ...
>> > I have to move 7 million records off the table - no biggie - I'll just
>> > dts the records to another server.
>> > My problem is deleting the remaining data to make room on the server
>> > without impacting/logging too much.
>> > Any help would be appreciated.
>> > Thanks,
>> > Craig
>> you can truncate the table one the data is moved out.
>
> I will only need to delete 7 m rows of the 33 m rows ... so I need a
> good way to do that.
> Craig
>|||On Tue, 11 Sep 2007 22:27:56 -0700, Craig wrote:
>On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
>> On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
>> > I have a large database - 33 million rows - VERY WIDE table and am
>> > running out of disk space - lovely ...
>> > I have to move 7 million records off the table - no biggie - I'll just
>> > dts the records to another server.
>> > My problem is deleting the remaining data to make room on the server
>> > without impacting/logging too much.
>> > Any help would be appreciated.
>> > Thanks,
>> > Craig
>> you can truncate the table one the data is moved out.
>
>I will only need to delete 7 m rows of the 33 m rows ... so I need a
>good way to do that.
>Craig
Hi Craig,
A common technique is to split the delete in batches, like this:
DECLARE @.rc int;
SET @.rc = 1;
WHILE @.rc <> 0
BEGIN;
DELETE TOP(100000)
FROM YourTable
WHERE whatever has to be deleted;
SET @.rc = @.@.ROWCOUNT;
END;
In SQL Server 2000, DELETE TOP(..) is not supported - instead, use SET
ROWCOUNT 100000 in the beginning and SET ROWCOUNT 0 at the end of the
script. Also, for all versions, you might need to experiment to find the
ideal batch size.
IIf your recovery model is full, either switch temporarily to simple (as
sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||> IIf your recovery model is full, either switch temporarily to simple (as
> sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
Now, above doesn't jive in a SQL Server forum. I think you meant:
CASE WHEN recovery model is full THEN switch temporarily to simple ...
(Sorry, I couldn't resist. Just playing a bad joke with Hugo, doesn't have anything to do with your
problem, Craig...)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:60tge3pj8h8jk3d3kohjahp2gh6s345595@.4ax.com...
> On Tue, 11 Sep 2007 22:27:56 -0700, Craig wrote:
>>On Sep 11, 9:17 pm, SB <othell...@.yahoo.com> wrote:
>> On Sep 12, 9:17 am, Craig <csomb...@.gmail.com> wrote:
>> > I have a large database - 33 million rows - VERY WIDE table and am
>> > running out of disk space - lovely ...
>> > I have to move 7 million records off the table - no biggie - I'll just
>> > dts the records to another server.
>> > My problem is deleting the remaining data to make room on the server
>> > without impacting/logging too much.
>> > Any help would be appreciated.
>> > Thanks,
>> > Craig
>> you can truncate the table one the data is moved out.
>>
>>I will only need to delete 7 m rows of the 33 m rows ... so I need a
>>good way to do that.
>>Craig
> Hi Craig,
> A common technique is to split the delete in batches, like this:
> DECLARE @.rc int;
> SET @.rc = 1;
> WHILE @.rc <> 0
> BEGIN;
> DELETE TOP(100000)
> FROM YourTable
> WHERE whatever has to be deleted;
> SET @.rc = @.@.ROWCOUNT;
> END;
> In SQL Server 2000, DELETE TOP(..) is not supported - instead, use SET
> ROWCOUNT 100000 in the beginning and SET ROWCOUNT 0 at the end of the
> script. Also, for all versions, you might need to experiment to find the
> ideal batch size.
> IIf your recovery model is full, either switch temporarily to simple (as
> sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Thu, 13 Sep 2007 09:52:56 +0200, Tibor Karaszi wrote:
>> IIf your recovery model is full, either switch temporarily to simple (as
>> sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
>Now, above doesn't jive in a SQL Server forum. I think you meant:
>CASE WHEN recovery model is full THEN switch temporarily to simple ...
Hey, Tibor,
You are not actually using CASE as a *statement*, now are you? Off you
go to the nearest C++ newsgroup!!
>(Sorry, I couldn't resist. Just playing a bad joke with Hugo, doesn't have anything to do with your
>problem, Craig...)
And neither could I ... :-)
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||> You are not actually using CASE as a *statement*, now are you? Off you
> go to the nearest C++ newsgroup!!
I guess my wishes for a closer integration with ANSI SQL PSM syntax influences me... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:rhije3lgrsc2476uhtfig6ue17iiv3b1s8@.4ax.com...
> On Thu, 13 Sep 2007 09:52:56 +0200, Tibor Karaszi wrote:
>> IIf your recovery model is full, either switch temporarily to simple (as
>> sugggested by Ekrem), or add a BACKUP LOG command inside the WHILE loop.
>>Now, above doesn't jive in a SQL Server forum. I think you meant:
>>CASE WHEN recovery model is full THEN switch temporarily to simple ...
> Hey, Tibor,
> You are not actually using CASE as a *statement*, now are you? Off you
> go to the nearest C++ newsgroup!!
>>(Sorry, I couldn't resist. Just playing a bad joke with Hugo, doesn't have anything to do with
>>your
>>problem, Craig...)
> And neither could I ... :-)
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Friday, March 9, 2012

Deleted SQL Resouce

i was deleting a disk resource from our cluster, the sql resource depended on that disk, when i was asked about deleting the dependent resources i clicked the delete option, so now i dont have any sql resourse. now i can′t see my SQL anymore even whe i
try to create a sql resource manually.
What can i do?
Thanks
I think you should
- copy the mdf and ldf file off to a safe location
- uninstall sql server from the cluster
- reinstall sql server to the cluster
- attach back the copied mdf and ldf files.
I don't think you can create the sql server resource without going through
the setup, but I could be wrong.
Vikram
"SQL Resource Deleted... Help!!!!" <SQL Resource Deleted...
Help!!!!@.discussions.microsoft.com> wrote in message
news:65D535D5-599A-4568-9E68-BFD2C496F73D@.microsoft.com...
> i was deleting a disk resource from our cluster, the sql resource depended
on that disk, when i was asked about deleting the dependent resources i
clicked the delete option, so now i dont have any sql resourse. now i cant
see my SQL anymore even whe i try to create a sql resource manually.
> What can i do?
> Thanks
>
|||Hi...
create SQL resource using below scripts.
It will create 3 sql resources and set proper private property values.
before you execute scripts, you should change what i comment
It will help you. and really works!!!
@.Echo OFF
Echo ************************************************** ****
Echo * *
Echo * Create "SQL Server" Resource *
Echo * *
Echo ************************************************** ****
@.Echo ON
cluster . Resource "SQL Server" /Create /Group:"SQLVS1 Group" /Type:"SQL
Server" <= Change group name
cluster . Resource "SQL Server" /AddDependency:"SQL Network Name(SQLVS1)" <=
Change network name
cluster . Resource "SQL Server" /AddDependency:"Physical Disk(Disk S

" <=
Change Disk name
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\Rep lication"
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\SQL serverAgent"
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\Clu ster"
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\MSS QLSERVER"
cluster . Resource "SQL Server"
/AddCheckpoints:"SOFTWARE\Microsoft\MSSQLSERVER\PRO VIDERS"
cluster . Resource "SQL Server" /Priv VirtualServerName="SQLVS1" <= Change
SQL Server VS name
cluster . Resource "SQL Server" /Priv InstanceName="MSSQLSERVER"
cluster . Resource "SQL Server" /Priv RestartAction="2"
@.Echo OFF
Echo ************************************************** ****
Echo * *
Echo * Create "SQL Server Agent" Resource *
Echo * *
Echo ************************************************** ****
@.Echo ON
cluster . Resource "SQL Server Agent" /Create /Group:"SQLVS1 Group"
/Type:"SQL Server Agent" <= Change group name
cluster . Resource "SQL Server Agent" /AddDependency:"SQL Server"
cluster . Resource "SQL Server Agent" /Priv VirtualServerName="SQLVS1" <=
Change SQL Server VS name
cluster . Resource "SQL Server Agent" /Priv InstanceName="MSSQLSERVER"
cluster . Resource "SQL Server Agent" /Priv RestartAction="2"
@.Echo OFF
Echo ************************************************** ****
Echo * *
Echo * Create "SQL Server Fulltext" Resource *
Echo * *
Echo ************************************************** ****
@.Echo ON
cluster . Resource "SQL Server Fulltext" /Create /Group:"SQLVS1 Group"
/Type:"Microsoft Search Service Instance" <= Change group name
cluster . Resource "SQL Server Fulltext" /AddDependency:"SQL Server"
cluster . Resource "SQL Server Fulltext" /Priv ApplicationName="SQLServer"
cluster . Resource "SQL Server Fulltext" /Priv ApplicationPath="S:\Program
Files\Microsoft SQL Server\MSSQL\FTDATA" <= Change Drive and folder name
where SQL Database installs

Cheolwon Choi
Senior Consultant / Consulting Biz Unit / Feelanet Co., Ltd.
Microsoft MCSE/MCSA/MCT, HP MasterASE/ACI
"SQL Resource Deleted... Help!!!!" <SQL Resource Deleted...
Help!!!!@.discussions.microsoft.com> wrote in message
news:65D535D5-599A-4568-9E68-BFD2C496F73D@.microsoft.com...
> i was deleting a disk resource from our cluster, the sql resource depended
on that disk, when i was asked about deleting the dependent resources i
clicked the delete option, so now i dont have any sql resourse. now i
can′t see my SQL anymore even whe i try to create a sql resource manually.
> What can i do?
> Thanks
>

deleted db but mdf file still exists on disk

Hi,
I deleted a database but the mdf and the log files still exists on disk, I
tried to deleted but they are locked.
What can I do?
Can I delete the entries on sysaltfiles table, delete mdf and log files and
reboot the sql server?
Thanks in advance
PedroI suggest you check to ensure the files are not being used by other
databases. Check the output of the following:
EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Pedro" <Pedro@.discussions.microsoft.com> wrote in message
news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
> Hi,
> I deleted a database but the mdf and the log files still exists on disk, I
> tried to deleted but they are locked.
> What can I do?
> Can I delete the entries on sysaltfiles table, delete mdf and log files
> and
> reboot the sql server?
> Thanks in advance
> Pedro
>|||Hi Dan,
I can't define the database on USE statment because that database (DR2006)
was deleted.
At deletion time I had a disabled job for that database, maybe this was the
cause for problem?
"Dan Guzman" wrote:
> I suggest you check to ensure the files are not being used by other
> databases. Check the output of the following:
> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
> > Hi,
> >
> > I deleted a database but the mdf and the log files still exists on disk, I
> > tried to deleted but they are locked.
> > What can I do?
> > Can I delete the entries on sysaltfiles table, delete mdf and log files
> > and
> > reboot the sql server?
> >
> > Thanks in advance
> >
> > Pedro
> >
>
>|||What version of SQL Server?
First check the system tables. Does the database exist in sysdatabases? Any reference to the
database in sysaltfiles?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pedro" <Pedro@.discussions.microsoft.com> wrote in message
news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
> Hi Dan,
> I can't define the database on USE statment because that database (DR2006)
> was deleted.
> At deletion time I had a disabled job for that database, maybe this was the
> cause for problem?
> "Dan Guzman" wrote:
>> I suggest you check to ensure the files are not being used by other
>> databases. Check the output of the following:
>> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
>> > Hi,
>> >
>> > I deleted a database but the mdf and the log files still exists on disk, I
>> > tried to deleted but they are locked.
>> > What can I do?
>> > Can I delete the entries on sysaltfiles table, delete mdf and log files
>> > and
>> > reboot the sql server?
>> >
>> > Thanks in advance
>> >
>> > Pedro
>> >
>>|||Hi Tibor,
I am using SQL Server 2000 SP4.
No, does not exist any reference in sysdatabases.
Pedro
"Tibor Karaszi" wrote:
> What version of SQL Server?
> First check the system tables. Does the database exist in sysdatabases? Any reference to the
> database in sysaltfiles?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
> > Hi Dan,
> >
> > I can't define the database on USE statment because that database (DR2006)
> > was deleted.
> > At deletion time I had a disabled job for that database, maybe this was the
> > cause for problem?
> >
> > "Dan Guzman" wrote:
> >
> >> I suggest you check to ensure the files are not being used by other
> >> databases. Check the output of the following:
> >>
> >> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
> >> > Hi,
> >> >
> >> > I deleted a database but the mdf and the log files still exists on disk, I
> >> > tried to deleted but they are locked.
> >> > What can I do?
> >> > Can I delete the entries on sysaltfiles table, delete mdf and log files
> >> > and
> >> > reboot the sql server?
> >> >
> >> > Thanks in advance
> >> >
> >> > Pedro
> >> >
> >>
> >>
> >>
>|||What about sysaltfiles?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pedro" <Pedro@.discussions.microsoft.com> wrote in message
news:21004A82-C7FC-45A0-882E-8B0F2C3B7853@.microsoft.com...
> Hi Tibor,
> I am using SQL Server 2000 SP4.
> No, does not exist any reference in sysdatabases.
> Pedro
> "Tibor Karaszi" wrote:
>> What version of SQL Server?
>> First check the system tables. Does the database exist in sysdatabases? Any reference to the
>> database in sysaltfiles?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
>> > Hi Dan,
>> >
>> > I can't define the database on USE statment because that database (DR2006)
>> > was deleted.
>> > At deletion time I had a disabled job for that database, maybe this was the
>> > cause for problem?
>> >
>> > "Dan Guzman" wrote:
>> >
>> >> I suggest you check to ensure the files are not being used by other
>> >> databases. Check the output of the following:
>> >>
>> >> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
>> >>
>> >> --
>> >> Hope this helps.
>> >>
>> >> Dan Guzman
>> >> SQL Server MVP
>> >>
>> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> >> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > I deleted a database but the mdf and the log files still exists on disk, I
>> >> > tried to deleted but they are locked.
>> >> > What can I do?
>> >> > Can I delete the entries on sysaltfiles table, delete mdf and log files
>> >> > and
>> >> > reboot the sql server?
>> >> >
>> >> > Thanks in advance
>> >> >
>> >> > Pedro
>> >> >
>> >>
>> >>
>> >>
>>|||I think this is the problem.
sysaltfiles have entries for that database, i didn't find any other entry
anywhere for that database.
Pedro
"Tibor Karaszi" wrote:
> What about sysaltfiles?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> news:21004A82-C7FC-45A0-882E-8B0F2C3B7853@.microsoft.com...
> > Hi Tibor,
> >
> > I am using SQL Server 2000 SP4.
> > No, does not exist any reference in sysdatabases.
> >
> > Pedro
> >
> > "Tibor Karaszi" wrote:
> >
> >> What version of SQL Server?
> >>
> >> First check the system tables. Does the database exist in sysdatabases? Any reference to the
> >> database in sysaltfiles?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
> >> > Hi Dan,
> >> >
> >> > I can't define the database on USE statment because that database (DR2006)
> >> > was deleted.
> >> > At deletion time I had a disabled job for that database, maybe this was the
> >> > cause for problem?
> >> >
> >> > "Dan Guzman" wrote:
> >> >
> >> >> I suggest you check to ensure the files are not being used by other
> >> >> databases. Check the output of the following:
> >> >>
> >> >> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
> >> >>
> >> >> --
> >> >> Hope this helps.
> >> >>
> >> >> Dan Guzman
> >> >> SQL Server MVP
> >> >>
> >> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> >> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
> >> >> > Hi,
> >> >> >
> >> >> > I deleted a database but the mdf and the log files still exists on disk, I
> >> >> > tried to deleted but they are locked.
> >> >> > What can I do?
> >> >> > Can I delete the entries on sysaltfiles table, delete mdf and log files
> >> >> > and
> >> >> > reboot the sql server?
> >> >> >
> >> >> > Thanks in advance
> >> >> >
> >> >> > Pedro
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
>|||Hmm, so below query returns a number of rows?
select * from master..sysaltfiles sa
WHERE NOT EXISTS(SELECT * FROM master..sysdatabases sd WHERE sa.dbid = sd.dbid)
If so, I really don't know. I haven't come across that before. Maybe open a ticket with MS Support,
just to be certain you do the right thing here.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pedro" <Pedro@.discussions.microsoft.com> wrote in message
news:8AA0D07C-FBC3-4A2A-8B32-C191F4014FE1@.microsoft.com...
>I think this is the problem.
> sysaltfiles have entries for that database, i didn't find any other entry
> anywhere for that database.
> Pedro
> "Tibor Karaszi" wrote:
>> What about sysaltfiles?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> news:21004A82-C7FC-45A0-882E-8B0F2C3B7853@.microsoft.com...
>> > Hi Tibor,
>> >
>> > I am using SQL Server 2000 SP4.
>> > No, does not exist any reference in sysdatabases.
>> >
>> > Pedro
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> What version of SQL Server?
>> >>
>> >> First check the system tables. Does the database exist in sysdatabases? Any reference to the
>> >> database in sysaltfiles?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> >> news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
>> >> > Hi Dan,
>> >> >
>> >> > I can't define the database on USE statment because that database (DR2006)
>> >> > was deleted.
>> >> > At deletion time I had a disabled job for that database, maybe this was the
>> >> > cause for problem?
>> >> >
>> >> > "Dan Guzman" wrote:
>> >> >
>> >> >> I suggest you check to ensure the files are not being used by other
>> >> >> databases. Check the output of the following:
>> >> >>
>> >> >> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
>> >> >>
>> >> >> --
>> >> >> Hope this helps.
>> >> >>
>> >> >> Dan Guzman
>> >> >> SQL Server MVP
>> >> >>
>> >> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> >> >> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
>> >> >> > Hi,
>> >> >> >
>> >> >> > I deleted a database but the mdf and the log files still exists on disk, I
>> >> >> > tried to deleted but they are locked.
>> >> >> > What can I do?
>> >> >> > Can I delete the entries on sysaltfiles table, delete mdf and log files
>> >> >> > and
>> >> >> > reboot the sql server?
>> >> >> >
>> >> >> > Thanks in advance
>> >> >> >
>> >> >> > Pedro
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>>|||No, it returns 0 rows
"Tibor Karaszi" wrote:
> Hmm, so below query returns a number of rows?
> select * from master..sysaltfiles sa
> WHERE NOT EXISTS(SELECT * FROM master..sysdatabases sd WHERE sa.dbid = sd.dbid)
>
> If so, I really don't know. I haven't come across that before. Maybe open a ticket with MS Support,
> just to be certain you do the right thing here.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> news:8AA0D07C-FBC3-4A2A-8B32-C191F4014FE1@.microsoft.com...
> >I think this is the problem.
> > sysaltfiles have entries for that database, i didn't find any other entry
> > anywhere for that database.
> >
> > Pedro
> >
> > "Tibor Karaszi" wrote:
> >
> >> What about sysaltfiles?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> news:21004A82-C7FC-45A0-882E-8B0F2C3B7853@.microsoft.com...
> >> > Hi Tibor,
> >> >
> >> > I am using SQL Server 2000 SP4.
> >> > No, does not exist any reference in sysdatabases.
> >> >
> >> > Pedro
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> What version of SQL Server?
> >> >>
> >> >> First check the system tables. Does the database exist in sysdatabases? Any reference to the
> >> >> database in sysaltfiles?
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >> >>
> >> >>
> >> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> >> news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
> >> >> > Hi Dan,
> >> >> >
> >> >> > I can't define the database on USE statment because that database (DR2006)
> >> >> > was deleted.
> >> >> > At deletion time I had a disabled job for that database, maybe this was the
> >> >> > cause for problem?
> >> >> >
> >> >> > "Dan Guzman" wrote:
> >> >> >
> >> >> >> I suggest you check to ensure the files are not being used by other
> >> >> >> databases. Check the output of the following:
> >> >> >>
> >> >> >> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
> >> >> >>
> >> >> >> --
> >> >> >> Hope this helps.
> >> >> >>
> >> >> >> Dan Guzman
> >> >> >> SQL Server MVP
> >> >> >>
> >> >> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> >> >> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
> >> >> >> > Hi,
> >> >> >> >
> >> >> >> > I deleted a database but the mdf and the log files still exists on disk, I
> >> >> >> > tried to deleted but they are locked.
> >> >> >> > What can I do?
> >> >> >> > Can I delete the entries on sysaltfiles table, delete mdf and log files
> >> >> >> > and
> >> >> >> > reboot the sql server?
> >> >> >> >
> >> >> >> > Thanks in advance
> >> >> >> >
> >> >> >> > Pedro
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>|||Pedro,
The command I posted will not try to USE your deleted database. It will
list all files used by any database. After running it, check the file list
to ensure the files you are trying to delete are not used by other
databases.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Pedro" <Pedro@.discussions.microsoft.com> wrote in message
news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
> Hi Dan,
> I can't define the database on USE statment because that database (DR2006)
> was deleted.
> At deletion time I had a disabled job for that database, maybe this was
> the
> cause for problem?
> "Dan Guzman" wrote:
>> I suggest you check to ensure the files are not being used by other
>> databases. Check the output of the following:
>> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
>> > Hi,
>> >
>> > I deleted a database but the mdf and the log files still exists on
>> > disk, I
>> > tried to deleted but they are locked.
>> > What can I do?
>> > Can I delete the entries on sysaltfiles table, delete mdf and log files
>> > and
>> > reboot the sql server?
>> >
>> > Thanks in advance
>> >
>> > Pedro
>> >
>>|||Then how would you say that there exists a database in sysaltfiles and not in sysdatabases? Check
the id in column in sysaltfiles, and which row that correspond in sysdatabases. There is your
database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pedro" <Pedro@.discussions.microsoft.com> wrote in message
news:099D71F3-D668-4B2B-80C5-D1CB6B0B9104@.microsoft.com...
> No, it returns 0 rows
> "Tibor Karaszi" wrote:
>> Hmm, so below query returns a number of rows?
>> select * from master..sysaltfiles sa
>> WHERE NOT EXISTS(SELECT * FROM master..sysdatabases sd WHERE sa.dbid = sd.dbid)
>>
>> If so, I really don't know. I haven't come across that before. Maybe open a ticket with MS
>> Support,
>> just to be certain you do the right thing here.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> news:8AA0D07C-FBC3-4A2A-8B32-C191F4014FE1@.microsoft.com...
>> >I think this is the problem.
>> > sysaltfiles have entries for that database, i didn't find any other entry
>> > anywhere for that database.
>> >
>> > Pedro
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> What about sysaltfiles?
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> >> news:21004A82-C7FC-45A0-882E-8B0F2C3B7853@.microsoft.com...
>> >> > Hi Tibor,
>> >> >
>> >> > I am using SQL Server 2000 SP4.
>> >> > No, does not exist any reference in sysdatabases.
>> >> >
>> >> > Pedro
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> What version of SQL Server?
>> >> >>
>> >> >> First check the system tables. Does the database exist in sysdatabases? Any reference to
>> >> >> the
>> >> >> database in sysaltfiles?
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >> >>
>> >> >>
>> >> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> >> >> news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
>> >> >> > Hi Dan,
>> >> >> >
>> >> >> > I can't define the database on USE statment because that database (DR2006)
>> >> >> > was deleted.
>> >> >> > At deletion time I had a disabled job for that database, maybe this was the
>> >> >> > cause for problem?
>> >> >> >
>> >> >> > "Dan Guzman" wrote:
>> >> >> >
>> >> >> >> I suggest you check to ensure the files are not being used by other
>> >> >> >> databases. Check the output of the following:
>> >> >> >>
>> >> >> >> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
>> >> >> >>
>> >> >> >> --
>> >> >> >> Hope this helps.
>> >> >> >>
>> >> >> >> Dan Guzman
>> >> >> >> SQL Server MVP
>> >> >> >>
>> >> >> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
>> >> >> >> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
>> >> >> >> > Hi,
>> >> >> >> >
>> >> >> >> > I deleted a database but the mdf and the log files still exists on disk, I
>> >> >> >> > tried to deleted but they are locked.
>> >> >> >> > What can I do?
>> >> >> >> > Can I delete the entries on sysaltfiles table, delete mdf and log files
>> >> >> >> > and
>> >> >> >> > reboot the sql server?
>> >> >> >> >
>> >> >> >> > Thanks in advance
>> >> >> >> >
>> >> >> >> > Pedro
>> >> >> >> >
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>>|||It's true.
Some time ago I created a new empty database (000) so when doing restore the
first database wasn't overwritten be mistake.
Some how 000 database was using the mdf files of database that I deleted
(DR2006), I can't explain how this happened.
But it's fixed now.
Thank you very much
Pedro
"Tibor Karaszi" wrote:
> Then how would you say that there exists a database in sysaltfiles and not in sysdatabases? Check
> the id in column in sysaltfiles, and which row that correspond in sysdatabases. There is your
> database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> news:099D71F3-D668-4B2B-80C5-D1CB6B0B9104@.microsoft.com...
> > No, it returns 0 rows
> >
> > "Tibor Karaszi" wrote:
> >
> >> Hmm, so below query returns a number of rows?
> >>
> >> select * from master..sysaltfiles sa
> >> WHERE NOT EXISTS(SELECT * FROM master..sysdatabases sd WHERE sa.dbid = sd.dbid)
> >>
> >>
> >> If so, I really don't know. I haven't come across that before. Maybe open a ticket with MS
> >> Support,
> >> just to be certain you do the right thing here.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> news:8AA0D07C-FBC3-4A2A-8B32-C191F4014FE1@.microsoft.com...
> >> >I think this is the problem.
> >> > sysaltfiles have entries for that database, i didn't find any other entry
> >> > anywhere for that database.
> >> >
> >> > Pedro
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> What about sysaltfiles?
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >> >>
> >> >>
> >> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> >> news:21004A82-C7FC-45A0-882E-8B0F2C3B7853@.microsoft.com...
> >> >> > Hi Tibor,
> >> >> >
> >> >> > I am using SQL Server 2000 SP4.
> >> >> > No, does not exist any reference in sysdatabases.
> >> >> >
> >> >> > Pedro
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> What version of SQL Server?
> >> >> >>
> >> >> >> First check the system tables. Does the database exist in sysdatabases? Any reference to
> >> >> >> the
> >> >> >> database in sysaltfiles?
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://www.solidqualitylearning.com/
> >> >> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >> >> >>
> >> >> >>
> >> >> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> >> >> news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
> >> >> >> > Hi Dan,
> >> >> >> >
> >> >> >> > I can't define the database on USE statment because that database (DR2006)
> >> >> >> > was deleted.
> >> >> >> > At deletion time I had a disabled job for that database, maybe this was the
> >> >> >> > cause for problem?
> >> >> >> >
> >> >> >> > "Dan Guzman" wrote:
> >> >> >> >
> >> >> >> >> I suggest you check to ensure the files are not being used by other
> >> >> >> >> databases. Check the output of the following:
> >> >> >> >>
> >> >> >> >> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
> >> >> >> >>
> >> >> >> >> --
> >> >> >> >> Hope this helps.
> >> >> >> >>
> >> >> >> >> Dan Guzman
> >> >> >> >> SQL Server MVP
> >> >> >> >>
> >> >> >> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> >> >> >> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
> >> >> >> >> > Hi,
> >> >> >> >> >
> >> >> >> >> > I deleted a database but the mdf and the log files still exists on disk, I
> >> >> >> >> > tried to deleted but they are locked.
> >> >> >> >> > What can I do?
> >> >> >> >> > Can I delete the entries on sysaltfiles table, delete mdf and log files
> >> >> >> >> > and
> >> >> >> >> > reboot the sql server?
> >> >> >> >> >
> >> >> >> >> > Thanks in advance
> >> >> >> >> >
> >> >> >> >> > Pedro
> >> >> >> >> >
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>|||Sorry,
I imagined that [?] was to be replaced by the database name.
I have fixed the problem.
Thank you.
All the best.
Pedro
"Dan Guzman" wrote:
> Pedro,
> The command I posted will not try to USE your deleted database. It will
> list all files used by any database. After running it, check the file list
> to ensure the files you are trying to delete are not used by other
> databases.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> news:1AD9CB8F-1303-4C5F-8A69-603DCA29847B@.microsoft.com...
> > Hi Dan,
> >
> > I can't define the database on USE statment because that database (DR2006)
> > was deleted.
> > At deletion time I had a disabled job for that database, maybe this was
> > the
> > cause for problem?
> >
> > "Dan Guzman" wrote:
> >
> >> I suggest you check to ensure the files are not being used by other
> >> databases. Check the output of the following:
> >>
> >> EXEC sp_MSforeachdb 'USE [?] EXEC sp_helpfile'
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "Pedro" <Pedro@.discussions.microsoft.com> wrote in message
> >> news:14719753-DEEF-4AAF-A18D-157526941F14@.microsoft.com...
> >> > Hi,
> >> >
> >> > I deleted a database but the mdf and the log files still exists on
> >> > disk, I
> >> > tried to deleted but they are locked.
> >> > What can I do?
> >> > Can I delete the entries on sysaltfiles table, delete mdf and log files
> >> > and
> >> > reboot the sql server?
> >> >
> >> > Thanks in advance
> >> >
> >> > Pedro
> >> >
> >>
> >>
> >>
>
>

Wednesday, March 7, 2012

delete/move transaction log

Hi all,

I'm an old Oracle dba that had to convert DB religion :-)

Quick question for you gurus.

If i have a disk that is starting to break down, but still working, and that disk has the Transaction log for the SQL2000 DB, is there an easy, quick way to move the transaction log to a different disk while we repair the disk ? As little downtime as possible is what i'm looking for.

Or do i have to ...
1. Full DB backup
2. Fix the disk
3. Full DB restore since the transactionlog is missing.

I have looked thru the SQL200 Admin Companion but i really miss i small whitepaper giving some general procedures what to do if you loose transction log disk, datafile disk etc etc.

Anyone have any good whitepapers or know where it can be downloaded ?

Hope this question isn't to trivial for you experts or that it has been asked before, i did search first.

Thanks.Hey there,

What you should be able to do is:

1. Shutdown applications and users accessing the database.
2. Detach the database and transaction log.
3. Copy the transaction log file(s) (.LDF) to your new drive.
4. ReAttach DB and log (with new location)
5. Allow users etc back in & fix disk.
6. Once disk fixed repeat process.

The longest part will be the file copy.. There is information in Books Online to help you. You can either use Enterprise manager or T-SQL.

Hope this helps.|||Thanks mate, sounds like good way to do it.

I'll have a look at the books online once more.

Cheers|||No worries,

The stored procs you will require should you choose T-SQL are: sp_attach_db & sp_detach_db. Search for info on these and you should find some info to help you..

Cheers|||This can also be done with no downtime...provided you have diskspace ;-).

1) Create a brand new transaction log on a good disk.
2) Run DBCC SHRINKFILE ((Old logfile id), EMPTYFILE)
This last command marks the old logfile as "not to be used anymore"
3) Backup any transactions that may be in the bad file.
4) Drop the bad logfile.

Good luck.

Tuesday, February 14, 2012

Delete not releasing disk space

I'm running sql 2000 standard on my ISA server for ISA to log to. ISA
generates a lot of data each day so I used DTS to backup data to access to
burn to dvd. Then I used a delete sql statement in query analyzer as such:
DELETE
FROM FireWallLog
WHERE LogDate BETWEEN CONVERT(DATETIME, '2004-10-18', 102) AND
CONVERT(DATETIME, '2004-11-14', 102)
After a good 10 minutes or so, it comes back with hundreds of thousands of
rows affected, which is good. I then look at the database MDF and LDF files
and see that the MDF file didn't shrink any and the LDF grew in size, up to
a gig in size.
It is my understanding from reading SQL Books online that the delete
statement is supposed to open up disk space immediately. It doesn't seem to
be the case. My hard drive is about full (again .. I cleared other space up
earlier because once it fills up, the proxy server starts to crash - hard).
Thanks.
Jim
Hi Jim
Massive deletes will deallocate space within the SQL Server files, for
re-use by the database, but will not remove space from the physical files.
Plus, as you noticed, deleting so many rows causing a lot of logging, which
could cause the log file to grow.
The only way to remove space from the files is to physically shrink the
database or its files.
Please read about DBCC SHRINKFILE and DBCC SHRINKDATABASE in the Books
Online.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jim in Arizona" <tiltowait@.hotmail.com> wrote in message
news:%23RcvGam0EHA.2824@.TK2MSFTNGP09.phx.gbl...
> I'm running sql 2000 standard on my ISA server for ISA to log to. ISA
> generates a lot of data each day so I used DTS to backup data to access to
> burn to dvd. Then I used a delete sql statement in query analyzer as such:
> DELETE
> FROM FireWallLog
> WHERE LogDate BETWEEN CONVERT(DATETIME, '2004-10-18', 102) AND
> CONVERT(DATETIME, '2004-11-14', 102)
> After a good 10 minutes or so, it comes back with hundreds of thousands of
> rows affected, which is good. I then look at the database MDF and LDF
> files and see that the MDF file didn't shrink any and the LDF grew in
> size, up to a gig in size.
> It is my understanding from reading SQL Books online that the delete
> statement is supposed to open up disk space immediately. It doesn't seem
> to be the case. My hard drive is about full (again .. I cleared other
> space up earlier because once it fills up, the proxy server starts to
> crash - hard).
> Thanks.
> Jim
>
>
>
|||In addition to Kalen's advice, please see:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://www.aspfaq.com/
(Reverse address to reply.)
"Jim in Arizona" <tiltowait@.hotmail.com> wrote in message
news:#RcvGam0EHA.2824@.TK2MSFTNGP09.phx.gbl...
> I'm running sql 2000 standard on my ISA server for ISA to log to. ISA
> generates a lot of data each day so I used DTS to backup data to access to
> burn to dvd. Then I used a delete sql statement in query analyzer as such:
> DELETE
> FROM FireWallLog
> WHERE LogDate BETWEEN CONVERT(DATETIME, '2004-10-18', 102) AND
> CONVERT(DATETIME, '2004-11-14', 102)
> After a good 10 minutes or so, it comes back with hundreds of thousands of
> rows affected, which is good. I then look at the database MDF and LDF
files
> and see that the MDF file didn't shrink any and the LDF grew in size, up
to
> a gig in size.
> It is my understanding from reading SQL Books online that the delete
> statement is supposed to open up disk space immediately. It doesn't seem
to
> be the case. My hard drive is about full (again .. I cleared other space
up
> earlier because once it fills up, the proxy server starts to crash -
hard).
> Thanks.
> Jim
>
>
>

Delete not releasing disk space

I'm running sql 2000 standard on my ISA server for ISA to log to. ISA
generates a lot of data each day so I used DTS to backup data to access to
burn to dvd. Then I used a delete sql statement in query analyzer as such:
DELETE
FROM FireWallLog
WHERE LogDate BETWEEN CONVERT(DATETIME, '2004-10-18', 102) AND
CONVERT(DATETIME, '2004-11-14', 102)
After a good 10 minutes or so, it comes back with hundreds of thousands of
rows affected, which is good. I then look at the database MDF and LDF files
and see that the MDF file didn't shrink any and the LDF grew in size, up to
a gig in size.
It is my understanding from reading SQL Books online that the delete
statement is supposed to open up disk space immediately. It doesn't seem to
be the case. My hard drive is about full (again .. I cleared other space up
earlier because once it fills up, the proxy server starts to crash - hard).
Thanks.
JimHi Jim
Massive deletes will deallocate space within the SQL Server files, for
re-use by the database, but will not remove space from the physical files.
Plus, as you noticed, deleting so many rows causing a lot of logging, which
could cause the log file to grow.
The only way to remove space from the files is to physically shrink the
database or its files.
Please read about DBCC SHRINKFILE and DBCC SHRINKDATABASE in the Books
Online.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jim in Arizona" <tiltowait@.hotmail.com> wrote in message
news:%23RcvGam0EHA.2824@.TK2MSFTNGP09.phx.gbl...
> I'm running sql 2000 standard on my ISA server for ISA to log to. ISA
> generates a lot of data each day so I used DTS to backup data to access to
> burn to dvd. Then I used a delete sql statement in query analyzer as such:
> DELETE
> FROM FireWallLog
> WHERE LogDate BETWEEN CONVERT(DATETIME, '2004-10-18', 102) AND
> CONVERT(DATETIME, '2004-11-14', 102)
> After a good 10 minutes or so, it comes back with hundreds of thousands of
> rows affected, which is good. I then look at the database MDF and LDF
> files and see that the MDF file didn't shrink any and the LDF grew in
> size, up to a gig in size.
> It is my understanding from reading SQL Books online that the delete
> statement is supposed to open up disk space immediately. It doesn't seem
> to be the case. My hard drive is about full (again .. I cleared other
> space up earlier because once it fills up, the proxy server starts to
> crash - hard).
> Thanks.
> Jim
>
>
>|||In addition to Kalen's advice, please see:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://www.aspfaq.com/
(Reverse address to reply.)
"Jim in Arizona" <tiltowait@.hotmail.com> wrote in message
news:#RcvGam0EHA.2824@.TK2MSFTNGP09.phx.gbl...
> I'm running sql 2000 standard on my ISA server for ISA to log to. ISA
> generates a lot of data each day so I used DTS to backup data to access to
> burn to dvd. Then I used a delete sql statement in query analyzer as such:
> DELETE
> FROM FireWallLog
> WHERE LogDate BETWEEN CONVERT(DATETIME, '2004-10-18', 102) AND
> CONVERT(DATETIME, '2004-11-14', 102)
> After a good 10 minutes or so, it comes back with hundreds of thousands of
> rows affected, which is good. I then look at the database MDF and LDF
files
> and see that the MDF file didn't shrink any and the LDF grew in size, up
to
> a gig in size.
> It is my understanding from reading SQL Books online that the delete
> statement is supposed to open up disk space immediately. It doesn't seem
to
> be the case. My hard drive is about full (again .. I cleared other space
up
> earlier because once it fills up, the proxy server starts to crash -
hard).
> Thanks.
> Jim
>
>
>

Delete not releasing disk space

I'm running sql 2000 standard on my ISA server for ISA to log to. ISA
generates a lot of data each day so I used DTS to backup data to access to
burn to dvd. Then I used a delete sql statement in query analyzer as such:
DELETE
FROM FireWallLog
WHERE LogDate BETWEEN CONVERT(DATETIME, '2004-10-18', 102) AND
CONVERT(DATETIME, '2004-11-14', 102)
After a good 10 minutes or so, it comes back with hundreds of thousands of
rows affected, which is good. I then look at the database MDF and LDF files
and see that the MDF file didn't shrink any and the LDF grew in size, up to
a gig in size.
It is my understanding from reading SQL Books online that the delete
statement is supposed to open up disk space immediately. It doesn't seem to
be the case. My hard drive is about full (again .. I cleared other space up
earlier because once it fills up, the proxy server starts to crash - hard).
Thanks.
JimHi Jim
Massive deletes will deallocate space within the SQL Server files, for
re-use by the database, but will not remove space from the physical files.
Plus, as you noticed, deleting so many rows causing a lot of logging, which
could cause the log file to grow.
The only way to remove space from the files is to physically shrink the
database or its files.
Please read about DBCC SHRINKFILE and DBCC SHRINKDATABASE in the Books
Online.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jim in Arizona" <tiltowait@.hotmail.com> wrote in message
news:%23RcvGam0EHA.2824@.TK2MSFTNGP09.phx.gbl...
> I'm running sql 2000 standard on my ISA server for ISA to log to. ISA
> generates a lot of data each day so I used DTS to backup data to access to
> burn to dvd. Then I used a delete sql statement in query analyzer as such:
> DELETE
> FROM FireWallLog
> WHERE LogDate BETWEEN CONVERT(DATETIME, '2004-10-18', 102) AND
> CONVERT(DATETIME, '2004-11-14', 102)
> After a good 10 minutes or so, it comes back with hundreds of thousands of
> rows affected, which is good. I then look at the database MDF and LDF
> files and see that the MDF file didn't shrink any and the LDF grew in
> size, up to a gig in size.
> It is my understanding from reading SQL Books online that the delete
> statement is supposed to open up disk space immediately. It doesn't seem
> to be the case. My hard drive is about full (again .. I cleared other
> space up earlier because once it fills up, the proxy server starts to
> crash - hard).
> Thanks.
> Jim
>
>
>|||In addition to Kalen's advice, please see:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Jim in Arizona" <tiltowait@.hotmail.com> wrote in message
news:#RcvGam0EHA.2824@.TK2MSFTNGP09.phx.gbl...
> I'm running sql 2000 standard on my ISA server for ISA to log to. ISA
> generates a lot of data each day so I used DTS to backup data to access to
> burn to dvd. Then I used a delete sql statement in query analyzer as such:
> DELETE
> FROM FireWallLog
> WHERE LogDate BETWEEN CONVERT(DATETIME, '2004-10-18', 102) AND
> CONVERT(DATETIME, '2004-11-14', 102)
> After a good 10 minutes or so, it comes back with hundreds of thousands of
> rows affected, which is good. I then look at the database MDF and LDF
files
> and see that the MDF file didn't shrink any and the LDF grew in size, up
to
> a gig in size.
> It is my understanding from reading SQL Books online that the delete
> statement is supposed to open up disk space immediately. It doesn't seem
to
> be the case. My hard drive is about full (again .. I cleared other space
up
> earlier because once it fills up, the proxy server starts to crash -
hard).
> Thanks.
> Jim
>
>
>

Thursday, March 29, 2012

Deleting records to save DISK Space...

Tuesday, March 27, 2012

Deleting Older Files

Wednesday, March 21, 2012

Deleting data with least impact

Friday, March 9, 2012

Deleted SQL Resouce

deleted db but mdf file still exists on disk

deleted db but mdf file still exists on disk

deleted db but mdf file still exists on disk

Wednesday, March 7, 2012

delete/move transaction log

Tuesday, February 14, 2012

Delete not releasing disk space

Delete not releasing disk space

Delete not releasing disk space

DENY permission,SQL server

Blog Archive

About Me