Can't shrink a Transaction Log File

I backed up my log file and attempted to shrink it with DBCC
SHRINKFILE. The command executes but leaves the transaction log at
it's current size. I know that an uncommited tansaction can cause this
to occur.
a) How do I test for this?
b)How do I get rid of it?
Regards
John L
Hi Gemel,
Try to backup your transaction log again and then try to shrink the file.
If its possible to loose youre trans log info(i guess you dont) change the
recovery model to simple(you'll loose your log info), shrink your tlog to the
desired size, then change recovery model to full and finally backup youre
database.
Hope that this can help you.
Regards
"gemel" wrote:

> I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L
>
|||If you have a long running open transaction that could prevent the log from
shrinking..
DBCC opentran will show you the oldest transaction still in transaction
state.
You might also try
DBCC SQLPERF(logspace) to see how much space in the log is used
also
DBCC loginfo ( google on this to find more details.)
hope this helps
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"gemel" <jonel@.glenavon1.demon.co.uk> wrote in message
news:gdhm31tl8rs5t2804ibhc9onv36lee5evb@.4ax.com...
>I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L

Can't shrink a Transaction Log File

I backed up my log file and attempted to shrink it with DBCC
SHRINKFILE. The command executes but leaves the transaction log at
it's current size. I know that an uncommited tansaction can cause this
to occur.
a) How do I test for this?
b)How do I get rid of it?
Regards
John LHi Gemel,
Try to backup your transaction log again and then try to shrink the file.
If its possible to loose youre trans log info(i guess you dont) change the
recovery model to simple(you'll loose your log info), shrink your tlog to the
desired size, then change recovery model to full and finally backup youre
database.
Hope that this can help you.
Regards
"gemel" wrote:
> I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L
>|||If you have a long running open transaction that could prevent the log from
shrinking..
DBCC opentran will show you the oldest transaction still in transaction
state.
You might also try
DBCC SQLPERF(logspace) to see how much space in the log is used
also
DBCC loginfo ( google on this to find more details.)
hope this helps
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"gemel" <jonel@.glenavon1.demon.co.uk> wrote in message
news:gdhm31tl8rs5t2804ibhc9onv36lee5evb@.4ax.com...
>I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L

Can't shrink a Transaction Log File

I backed up my log file and attempted to shrink it with DBCC
SHRINKFILE. The command executes but leaves the transaction log at
it's current size. I know that an uncommited tansaction can cause this
to occur.
a) How do I test for this?
b)How do I get rid of it?
Regards
John LHi Gemel,
Try to backup your transaction log again and then try to shrink the file.
If its possible to loose youre trans log info(i guess you dont) change the
recovery model to simple(you'll loose your log info), shrink your tlog to th
e
desired size, then change recovery model to full and finally backup youre
database.
Hope that this can help you.
Regards
"gemel" wrote:

> I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John L
>|||If you have a long running open transaction that could prevent the log from
shrinking..
DBCC opentran will show you the oldest transaction still in transaction
state.
You might also try
DBCC SQLPERF(logspace) to see how much space in the log is used
also
DBCC loginfo ( google on this to find more details.)
hope this helps
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"gemel" <jonel@.glenavon1.demon.co.uk> wrote in message
news:gdhm31tl8rs5t2804ibhc9onv36lee5evb@.
4ax.com...
>I backed up my log file and attempted to shrink it with DBCC
> SHRINKFILE. The command executes but leaves the transaction log at
> it's current size. I know that an uncommited tansaction can cause this
> to occur.
> a) How do I test for this?
> b)How do I get rid of it?
> Regards
> John Lsql

Can't Restore Database

For years I've been using SQL Server 2000. Before heading out on a recent trip, I backed up a database on it and copied this backup over to my laptop. On my laptop I installed SQL Server 2005 Express and successfully restored this backup. While traveling I did development work using this database and it all worked great.

Now that I'm home, I'd like to backup & restore the laptop's database back onto my server. I've since removed SQL Server 2000 and have instead installed SQL Server 2005 Express. I followed exactly the same procedure as before, first creating the required database and then restoring it.

But the restoration failed with the following error and I'm not sure what's wrong:

Restore failed for Server 'SERVER\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'Incentives' database. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476

If anyone has any ideas, I'd love to hear them!

Robert W.

Vancouver, BC

Perhaps a 'typo' is involved. Check the database name on the backup. Try:

Code Snippet

RESTORE FILELISTONLY
FROM DISK = {FilePathName}

|||

Arnie, I'm using the dialog box in the SQL Server Mgmt tool and picking everything so I doubt that it could be a typo problem.

Robert

|||

The error message indicates that the database you are attempting to restore does not exist in the backup file (media).

Do the two computers have the same Service Packs installed on SQL Express?

|||

Ah hah, they're not! The destination computer doesn't have SP2 installed. Correcting now! I'll keep you posted. Thanks, Arnie!!

Robert

|||

Hi Arnie,

Well, there's egg on my face! I updated SQL Server Express 2005 to SP2 and tried restoring. Didn't work. Then I wondered if perhaps I shouldn't have first created a blank DB, like I *thought* I had to do with the initial backup & restore. So I deleted the empty DB and then tried the restore from scratch. Worked perfectly!

Thanks for your time & patience with this!!

Robert W.

|||Thanks for letting us know that you were finally successful. Good luck.|||Thanks, Arnie. Incidentally, I can't connect to the SQL Server 2005 Express DB like I used to be able to before with the SQL Server 2000 one. It has been so long since I setup that old one. Are there some permissions or such that I need to set so that I can connect to it from my laptop?

Or am I perhaps not specifying the username correctly. In my web app, I have the following variables set in Web.config:

<add key="DataBaseServer" value="SERVER\SQLEXPRESS"/>
<add key="DataBase" value="Incentives"/>
<add key="UserId" value="SERVER\Inc_Admin"/>

FYI I've setup "Inc_Admin" as a special login on my server's SQL Server rather than just using "sa" as I did in the past. But I also tried "Inc_Admin" and "sa" as substitutes for "UserId" but neither worked. So I'm thinking that it is perhaps a permissions issue.

Robert
|||Arnie,

More egg on my face! I just found this: http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

And it solve the problem.

Robert

|||Egg salad sandwiches for lunch tomorrow!

Can't Restore a DB backed up with COPY_ONLY in Mgmt Studio

If I create a backup using the following T-SQL, is there a specific reason that I cannot restore it through Mangement Studio.

BACKUP DATABASE MyDB

TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MyDB_1.BAK'

WITH FORMAT, COPY_ONLY, NAME = 'Backup of MyDB'

I can still restore it using T-SQL but would like the conveinence of using Managment Studio. Any ideas? Is this a bug or is this by design? If by design, what would be the reason?

Any help is appreciated.

Are there any ideas on this one?|||SQL Server Management Studio does not support copy-only backups, as per BOL notes.

Can't Restore a DB backed up with COPY_ONLY in Mgmt Studio

If I create a backup using the following T-SQL, is there a specific reason that I cannot restore it through Mangement Studio.

BACKUP DATABASE MyDB

TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MyDB_1.BAK'

WITH FORMAT, COPY_ONLY, NAME = 'Backup of MyDB'

I can still restore it using T-SQL but would like the conveinence of using Managment Studio. Any ideas? Is this a bug or is this by design? If by design, what would be the reason?

Any help is appreciated.

Are there any ideas on this one?|||SQL Server Management Studio does not support copy-only backups, as per BOL notes.

Can't Kill Process

We unfortunately found out that our databases were not being backed up for 95
days on one of our servers because the backup job got "stuck" on one of the
databases. I don't know why. I stopped the job but now the backups will not
work because the backup job from May is still "in process" on that one
database.
I tried killing the process but it won't work because there is "a rollback
in process". I rebooted the server but that did not help.
Does anyone have any suggestions on how I can kill this process on this one
database?
Thanks,
David McCarterFYI -
A rollback takes AT LEAST the same amount of time as it did to perform
whatever operations a transaction was doing. So, if a process has been
running for 1 hour and you decide to roll it back, then it would take at
least another hour to undo whatever it had already done.
"dotNetDave" <dotNetDave@.discussions.microsoft.com> wrote in message
news:27D4013A-7E4E-493A-A56E-EA9AD1D1CF27@.microsoft.com...
> We unfortunately found out that our databases were not being backed up for
> 95
> days on one of our servers because the backup job got "stuck" on one of
> the
> databases. I don't know why. I stopped the job but now the backups will
> not
> work because the backup job from May is still "in process" on that one
> database.
> I tried killing the process but it won't work because there is "a
> rollback
> in process". I rebooted the server but that did not help.
> Does anyone have any suggestions on how I can kill this process on this
> one
> database?
> Thanks,
> David McCarter|||So are you saying the rollback is going to take 95 days? That's how long
the process was running before someone noticed it.
"The DBA" wrote:
> FYI -
> A rollback takes AT LEAST the same amount of time as it did to perform
> whatever operations a transaction was doing. So, if a process has been
> running for 1 hour and you decide to roll it back, then it would take at
> least another hour to undo whatever it had already done.
> "dotNetDave" <dotNetDave@.discussions.microsoft.com> wrote in message
> news:27D4013A-7E4E-493A-A56E-EA9AD1D1CF27@.microsoft.com...
> > We unfortunately found out that our databases were not being backed up for
> > 95
> > days on one of our servers because the backup job got "stuck" on one of
> > the
> > databases. I don't know why. I stopped the job but now the backups will
> > not
> > work because the backup job from May is still "in process" on that one
> > database.
> >
> > I tried killing the process but it won't work because there is "a
> > rollback
> > in process". I rebooted the server but that did not help.
> >
> > Does anyone have any suggestions on how I can kill this process on this
> > one
> > database?
> >
> > Thanks,
> > David McCarter
>

Can't Kill Process

We unfortunately found out that our databases were not being backed up for 95
days on one of our servers because the backup job got "stuck" on one of the
databases. I don't know why. I stopped the job but now the backups will not
work because the backup job from May is still "in process" on that one
database.
I tried killing the process but it won't work because there is "a rollback
in process". I rebooted the server but that did not help.
Does anyone have any suggestions on how I can kill this process on this one
database?
Thanks,
David McCarter
FYI -
A rollback takes AT LEAST the same amount of time as it did to perform
whatever operations a transaction was doing. So, if a process has been
running for 1 hour and you decide to roll it back, then it would take at
least another hour to undo whatever it had already done.
"dotNetDave" <dotNetDave@.discussions.microsoft.com> wrote in message
news:27D4013A-7E4E-493A-A56E-EA9AD1D1CF27@.microsoft.com...
> We unfortunately found out that our databases were not being backed up for
> 95
> days on one of our servers because the backup job got "stuck" on one of
> the
> databases. I don't know why. I stopped the job but now the backups will
> not
> work because the backup job from May is still "in process" on that one
> database.
> I tried killing the process but it won't work because there is "a
> rollback
> in process". I rebooted the server but that did not help.
> Does anyone have any suggestions on how I can kill this process on this
> one
> database?
> Thanks,
> David McCarter
|||So are you saying the rollback is going to take 95 days? That's how long
the process was running before someone noticed it.
"The DBA" wrote:

> FYI -
> A rollback takes AT LEAST the same amount of time as it did to perform
> whatever operations a transaction was doing. So, if a process has been
> running for 1 hour and you decide to roll it back, then it would take at
> least another hour to undo whatever it had already done.
> "dotNetDave" <dotNetDave@.discussions.microsoft.com> wrote in message
> news:27D4013A-7E4E-493A-A56E-EA9AD1D1CF27@.microsoft.com...
>

Can't Kill Process

We unfortunately found out that our databases were not being backed up for 9
5
days on one of our servers because the backup job got "stuck" on one of the
databases. I don't know why. I stopped the job but now the backups will not
work because the backup job from May is still "in process" on that one
database.
I tried killing the process but it won't work because there is "a rollback
in process". I rebooted the server but that did not help.
Does anyone have any suggestions on how I can kill this process on this one
database?
Thanks,
David McCarterFYI -
A rollback takes AT LEAST the same amount of time as it did to perform
whatever operations a transaction was doing. So, if a process has been
running for 1 hour and you decide to roll it back, then it would take at
least another hour to undo whatever it had already done.
"dotNetDave" <dotNetDave@.discussions.microsoft.com> wrote in message
news:27D4013A-7E4E-493A-A56E-EA9AD1D1CF27@.microsoft.com...
> We unfortunately found out that our databases were not being backed up for
> 95
> days on one of our servers because the backup job got "stuck" on one of
> the
> databases. I don't know why. I stopped the job but now the backups will
> not
> work because the backup job from May is still "in process" on that one
> database.
> I tried killing the process but it won't work because there is "a
> rollback
> in process". I rebooted the server but that did not help.
> Does anyone have any suggestions on how I can kill this process on this
> one
> database?
> Thanks,
> David McCarter|||So are you saying the rollback is going to take 95 days? That's how long
the process was running before someone noticed it.
"The DBA" wrote:

> FYI -
> A rollback takes AT LEAST the same amount of time as it did to perform
> whatever operations a transaction was doing. So, if a process has been
> running for 1 hour and you decide to roll it back, then it would take at
> least another hour to undo whatever it had already done.
> "dotNetDave" <dotNetDave@.discussions.microsoft.com> wrote in message
> news:27D4013A-7E4E-493A-A56E-EA9AD1D1CF27@.microsoft.com...
>