List availables tables: across

Showing posts with label across. Show all posts

Monday, March 26, 2012

Live server running out of space - Can't reduce Transaction Log!

Hi,
I have just come across a bit of a problem of my Windows 200 server running
SQL Server 2000....I am running out of space!
I have aout 2GB left. I have know this for some time, and for technical
reasons just found out I can't add a new disk...but that's another story.
I have looked at what I can reduce to increase the space, and it seems one
of the smaller database I have on the server, i.e. 16MB, has a log file of
4.8GB!
I am not sure why this has occured, possibly due to not setting a checkpoint
during backup or something!?!?!
...anyway I am desperately tying to reduce the size of this file, and have
tried running the following:
USE Comms
GO
DBCC SHRINKFILE (Comms_log, 10)
and it just comes back and tells me:
- CurrentSize 608616
- Minimum Size 63
- UsedPages 608616
- EstimatedPages 56
with no reduction in LogFile size.
I also tried backing up just the databse and then restoring it under a
different name (so I can completely delete the old one and it's log!), but
when I try to restore it the system just comes back and tells me it hasn't
got enough space, even though it has just over 2GB (presumably it is trying,
or thinks it is trying to restore the log file as well, but goodness knows
where it's getting it from?!).
Any ideas?
Cheers, desperate Mike.Mike,
What is the database recovery model? Is the log space unrestricted? Do you
take log backups?
If this is a Full recovery model DB then take a log backup. If the log in
unrestricted filegrowth give it a maximum size, just bigger than it is now.
Then you need to follow this: - http://support.microsoft.com/kb/873235/en-us
Chris
"Mike Owen" <whatnospam@.nospam.nospam> wrote in message
news:8CFFFB57-6B03-4DA3-95E7-BFD6B27C95A3@.microsoft.com...
> Hi,
> I have just come across a bit of a problem of my Windows 200 server
> running
> SQL Server 2000....I am running out of space!
> I have aout 2GB left. I have know this for some time, and for technical
> reasons just found out I can't add a new disk...but that's another story.
> I have looked at what I can reduce to increase the space, and it seems one
> of the smaller database I have on the server, i.e. 16MB, has a log file of
> 4.8GB!
> I am not sure why this has occured, possibly due to not setting a
> checkpoint
> during backup or something!?!?!
> ...anyway I am desperately tying to reduce the size of this file, and have
> tried running the following:
> USE Comms
> GO
> DBCC SHRINKFILE (Comms_log, 10)
> and it just comes back and tells me:
> - CurrentSize 608616
> - Minimum Size 63
> - UsedPages 608616
> - EstimatedPages 56
> with no reduction in LogFile size.
> I also tried backing up just the databse and then restoring it under a
> different name (so I can completely delete the old one and it's log!), but
> when I try to restore it the system just comes back and tells me it hasn't
> got enough space, even though it has just over 2GB (presumably it is
> trying,
> or thinks it is trying to restore the log file as well, but goodness knows
> where it's getting it from?!).
> Any ideas?
> Cheers, desperate Mike.
>|||Hi Mike,
Just do a transaction log backup and then try the shrink operation again.
If that does not work check if you have transactions or jobs running for a
long time.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
> Hi,
> I have just come across a bit of a problem of my Windows 200 server running
> SQL Server 2000....I am running out of space!
> I have aout 2GB left. I have know this for some time, and for technical
> reasons just found out I can't add a new disk...but that's another story.
> I have looked at what I can reduce to increase the space, and it seems one
> of the smaller database I have on the server, i.e. 16MB, has a log file of
> 4.8GB!
> I am not sure why this has occured, possibly due to not setting a checkpoint
> during backup or something!?!?!
> ...anyway I am desperately tying to reduce the size of this file, and have
> tried running the following:
> USE Comms
> GO
> DBCC SHRINKFILE (Comms_log, 10)
> and it just comes back and tells me:
> - CurrentSize 608616
> - Minimum Size 63
> - UsedPages 608616
> - EstimatedPages 56
> with no reduction in LogFile size.
> I also tried backing up just the databse and then restoring it under a
> different name (so I can completely delete the old one and it's log!), but
> when I try to restore it the system just comes back and tells me it hasn't
> got enough space, even though it has just over 2GB (presumably it is trying,
> or thinks it is trying to restore the log file as well, but goodness knows
> where it's getting it from?!).
> Any ideas?
> Cheers, desperate Mike.
>|||Thanks for you reply Ben, and you Chris. I don't think I can take a log file
backup because I can oly do it on the local server and I haven't the space,
i.e. 2GB space and 4GB+ log file.
I have got a netwrok drive to a different server with space, but the SQL
server backp program doesn't see netwrok drives.
Any other ideas or a way I can backup to the network drive?
Cheers, Mike.
"Ben Nevarez" wrote:
> Hi Mike,
> Just do a transaction log backup and then try the shrink operation again.
> If that does not work check if you have transactions or jobs running for a
> long time.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
> > Hi,
> >
> > I have just come across a bit of a problem of my Windows 200 server running
> > SQL Server 2000....I am running out of space!
> >
> > I have aout 2GB left. I have know this for some time, and for technical
> > reasons just found out I can't add a new disk...but that's another story.
> >
> > I have looked at what I can reduce to increase the space, and it seems one
> > of the smaller database I have on the server, i.e. 16MB, has a log file of
> > 4.8GB!
> >
> > I am not sure why this has occured, possibly due to not setting a checkpoint
> > during backup or something!?!?!
> >
> > ...anyway I am desperately tying to reduce the size of this file, and have
> > tried running the following:
> >
> > USE Comms
> > GO
> > DBCC SHRINKFILE (Comms_log, 10)
> >
> > and it just comes back and tells me:
> > - CurrentSize 608616
> > - Minimum Size 63
> > - UsedPages 608616
> > - EstimatedPages 56
> >
> > with no reduction in LogFile size.
> >
> > I also tried backing up just the databse and then restoring it under a
> > different name (so I can completely delete the old one and it's log!), but
> > when I try to restore it the system just comes back and tells me it hasn't
> > got enough space, even though it has just over 2GB (presumably it is trying,
> > or thinks it is trying to restore the log file as well, but goodness knows
> > where it's getting it from?!).
> >
> > Any ideas?
> >
> > Cheers, desperate Mike.
> >|||If you do not have enough disk space then use the TRUNCATE_ONLY option like
in
backup log my_db with truncate_only
We are supposing you are not running in the SIMPLE recovery model. Is this
correct?
If that works, you must do a database backup at the end because by using
TRUNCATE_ONLY you are breaking the log chain.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
> Thanks for you reply Ben, and you Chris. I don't think I can take a log file
> backup because I can oly do it on the local server and I haven't the space,
> i.e. 2GB space and 4GB+ log file.
> I have got a netwrok drive to a different server with space, but the SQL
> server backp program doesn't see netwrok drives.
> Any other ideas or a way I can backup to the network drive?
>
> Cheers, Mike.
> "Ben Nevarez" wrote:
> >
> > Hi Mike,
> >
> > Just do a transaction log backup and then try the shrink operation again.
> >
> > If that does not work check if you have transactions or jobs running for a
> > long time.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Mike Owen" wrote:
> >
> > > Hi,
> > >
> > > I have just come across a bit of a problem of my Windows 200 server running
> > > SQL Server 2000....I am running out of space!
> > >
> > > I have aout 2GB left. I have know this for some time, and for technical
> > > reasons just found out I can't add a new disk...but that's another story.
> > >
> > > I have looked at what I can reduce to increase the space, and it seems one
> > > of the smaller database I have on the server, i.e. 16MB, has a log file of
> > > 4.8GB!
> > >
> > > I am not sure why this has occured, possibly due to not setting a checkpoint
> > > during backup or something!?!?!
> > >
> > > ...anyway I am desperately tying to reduce the size of this file, and have
> > > tried running the following:
> > >
> > > USE Comms
> > > GO
> > > DBCC SHRINKFILE (Comms_log, 10)
> > >
> > > and it just comes back and tells me:
> > > - CurrentSize 608616
> > > - Minimum Size 63
> > > - UsedPages 608616
> > > - EstimatedPages 56
> > >
> > > with no reduction in LogFile size.
> > >
> > > I also tried backing up just the databse and then restoring it under a
> > > different name (so I can completely delete the old one and it's log!), but
> > > when I try to restore it the system just comes back and tells me it hasn't
> > > got enough space, even though it has just over 2GB (presumably it is trying,
> > > or thinks it is trying to restore the log file as well, but goodness knows
> > > where it's getting it from?!).
> > >
> > > Any ideas?
> > >
> > > Cheers, desperate Mike.
> > >|||Excuse my ignorance - It knows no bounds!
How do I know if I am using the SIMPLE recovery model?
I am quite happy to use: backup log my_db with truncate_only
The database isn't used very much, and I only backed it up about 1 1/2 hours
ago, so as long as I can recover that should something go wrong that would be
fine....but perhaps that isn't possible with the backup I have?!!?
Cheers, Mike.
"Ben Nevarez" wrote:
> If you do not have enough disk space then use the TRUNCATE_ONLY option like
> in
> backup log my_db with truncate_only
> We are supposing you are not running in the SIMPLE recovery model. Is this
> correct?
> If that works, you must do a database backup at the end because by using
> TRUNCATE_ONLY you are breaking the log chain.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
> > Thanks for you reply Ben, and you Chris. I don't think I can take a log file
> > backup because I can oly do it on the local server and I haven't the space,
> > i.e. 2GB space and 4GB+ log file.
> >
> > I have got a netwrok drive to a different server with space, but the SQL
> > server backp program doesn't see netwrok drives.
> >
> > Any other ideas or a way I can backup to the network drive?
> >
> >
> > Cheers, Mike.
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > Hi Mike,
> > >
> > > Just do a transaction log backup and then try the shrink operation again.
> > >
> > > If that does not work check if you have transactions or jobs running for a
> > > long time.
> > >
> > > Hope this helps,
> > >
> > > Ben Nevarez
> > > Senior Database Administrator
> > > AIG SunAmerica
> > >
> > >
> > >
> > > "Mike Owen" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have just come across a bit of a problem of my Windows 200 server running
> > > > SQL Server 2000....I am running out of space!
> > > >
> > > > I have aout 2GB left. I have know this for some time, and for technical
> > > > reasons just found out I can't add a new disk...but that's another story.
> > > >
> > > > I have looked at what I can reduce to increase the space, and it seems one
> > > > of the smaller database I have on the server, i.e. 16MB, has a log file of
> > > > 4.8GB!
> > > >
> > > > I am not sure why this has occured, possibly due to not setting a checkpoint
> > > > during backup or something!?!?!
> > > >
> > > > ...anyway I am desperately tying to reduce the size of this file, and have
> > > > tried running the following:
> > > >
> > > > USE Comms
> > > > GO
> > > > DBCC SHRINKFILE (Comms_log, 10)
> > > >
> > > > and it just comes back and tells me:
> > > > - CurrentSize 608616
> > > > - Minimum Size 63
> > > > - UsedPages 608616
> > > > - EstimatedPages 56
> > > >
> > > > with no reduction in LogFile size.
> > > >
> > > > I also tried backing up just the databse and then restoring it under a
> > > > different name (so I can completely delete the old one and it's log!), but
> > > > when I try to restore it the system just comes back and tells me it hasn't
> > > > got enough space, even though it has just over 2GB (presumably it is trying,
> > > > or thinks it is trying to restore the log file as well, but goodness knows
> > > > where it's getting it from?!).
> > > >
> > > > Any ideas?
> > > >
> > > > Cheers, desperate Mike.
> > > >|||Looks like you are not using the SIMPLE recovery model but try this
select databasepropertyex('my_db', 'recovery')
And also try the backup log with truncate_only and then shrink the log files.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
> Excuse my ignorance - It knows no bounds!
> How do I know if I am using the SIMPLE recovery model?
> I am quite happy to use: backup log my_db with truncate_only
> The database isn't used very much, and I only backed it up about 1 1/2 hours
> ago, so as long as I can recover that should something go wrong that would be
> fine....but perhaps that isn't possible with the backup I have?!!?
>
> Cheers, Mike.
> "Ben Nevarez" wrote:
> >
> > If you do not have enough disk space then use the TRUNCATE_ONLY option like
> > in
> >
> > backup log my_db with truncate_only
> >
> > We are supposing you are not running in the SIMPLE recovery model. Is this
> > correct?
> >
> > If that works, you must do a database backup at the end because by using
> > TRUNCATE_ONLY you are breaking the log chain.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Mike Owen" wrote:
> >
> > > Thanks for you reply Ben, and you Chris. I don't think I can take a log file
> > > backup because I can oly do it on the local server and I haven't the space,
> > > i.e. 2GB space and 4GB+ log file.
> > >
> > > I have got a netwrok drive to a different server with space, but the SQL
> > > server backp program doesn't see netwrok drives.
> > >
> > > Any other ideas or a way I can backup to the network drive?
> > >
> > >
> > > Cheers, Mike.
> > >
> > > "Ben Nevarez" wrote:
> > >
> > > >
> > > > Hi Mike,
> > > >
> > > > Just do a transaction log backup and then try the shrink operation again.
> > > >
> > > > If that does not work check if you have transactions or jobs running for a
> > > > long time.
> > > >
> > > > Hope this helps,
> > > >
> > > > Ben Nevarez
> > > > Senior Database Administrator
> > > > AIG SunAmerica
> > > >
> > > >
> > > >
> > > > "Mike Owen" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I have just come across a bit of a problem of my Windows 200 server running
> > > > > SQL Server 2000....I am running out of space!
> > > > >
> > > > > I have aout 2GB left. I have know this for some time, and for technical
> > > > > reasons just found out I can't add a new disk...but that's another story.
> > > > >
> > > > > I have looked at what I can reduce to increase the space, and it seems one
> > > > > of the smaller database I have on the server, i.e. 16MB, has a log file of
> > > > > 4.8GB!
> > > > >
> > > > > I am not sure why this has occured, possibly due to not setting a checkpoint
> > > > > during backup or something!?!?!
> > > > >
> > > > > ...anyway I am desperately tying to reduce the size of this file, and have
> > > > > tried running the following:
> > > > >
> > > > > USE Comms
> > > > > GO
> > > > > DBCC SHRINKFILE (Comms_log, 10)
> > > > >
> > > > > and it just comes back and tells me:
> > > > > - CurrentSize 608616
> > > > > - Minimum Size 63
> > > > > - UsedPages 608616
> > > > > - EstimatedPages 56
> > > > >
> > > > > with no reduction in LogFile size.
> > > > >
> > > > > I also tried backing up just the databse and then restoring it under a
> > > > > different name (so I can completely delete the old one and it's log!), but
> > > > > when I try to restore it the system just comes back and tells me it hasn't
> > > > > got enough space, even though it has just over 2GB (presumably it is trying,
> > > > > or thinks it is trying to restore the log file as well, but goodness knows
> > > > > where it's getting it from?!).
> > > > >
> > > > > Any ideas?
> > > > >
> > > > > Cheers, desperate Mike.
> > > > >|||The answer to select databasepropertyex('my_db', 'recovery')
is FULL.
I tried the '...try the backup log with truncate_only and then shrink the
log files' as you suggested and it WORKED!
I am now back to 7GB of space, plenty of room for growth for all the DB's,
and have restricted the size of all log files on the server.
Thanks for all your help, you got me out of a scrape!
Have a good weekend, cheers, Mike.
"Ben Nevarez" wrote:
> Looks like you are not using the SIMPLE recovery model but try this
> select databasepropertyex('my_db', 'recovery')
> And also try the backup log with truncate_only and then shrink the log files.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
> > Excuse my ignorance - It knows no bounds!
> >
> > How do I know if I am using the SIMPLE recovery model?
> >
> > I am quite happy to use: backup log my_db with truncate_only
> >
> > The database isn't used very much, and I only backed it up about 1 1/2 hours
> > ago, so as long as I can recover that should something go wrong that would be
> > fine....but perhaps that isn't possible with the backup I have?!!?
> >
> >
> > Cheers, Mike.
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > If you do not have enough disk space then use the TRUNCATE_ONLY option like
> > > in
> > >
> > > backup log my_db with truncate_only
> > >
> > > We are supposing you are not running in the SIMPLE recovery model. Is this
> > > correct?
> > >
> > > If that works, you must do a database backup at the end because by using
> > > TRUNCATE_ONLY you are breaking the log chain.
> > >
> > > Hope this helps,
> > >
> > > Ben Nevarez
> > > Senior Database Administrator
> > > AIG SunAmerica
> > >
> > >
> > >
> > > "Mike Owen" wrote:
> > >
> > > > Thanks for you reply Ben, and you Chris. I don't think I can take a log file
> > > > backup because I can oly do it on the local server and I haven't the space,
> > > > i.e. 2GB space and 4GB+ log file.
> > > >
> > > > I have got a netwrok drive to a different server with space, but the SQL
> > > > server backp program doesn't see netwrok drives.
> > > >
> > > > Any other ideas or a way I can backup to the network drive?
> > > >
> > > >
> > > > Cheers, Mike.
> > > >
> > > > "Ben Nevarez" wrote:
> > > >
> > > > >
> > > > > Hi Mike,
> > > > >
> > > > > Just do a transaction log backup and then try the shrink operation again.
> > > > >
> > > > > If that does not work check if you have transactions or jobs running for a
> > > > > long time.
> > > > >
> > > > > Hope this helps,
> > > > >
> > > > > Ben Nevarez
> > > > > Senior Database Administrator
> > > > > AIG SunAmerica
> > > > >
> > > > >
> > > > >
> > > > > "Mike Owen" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I have just come across a bit of a problem of my Windows 200 server running
> > > > > > SQL Server 2000....I am running out of space!
> > > > > >
> > > > > > I have aout 2GB left. I have know this for some time, and for technical
> > > > > > reasons just found out I can't add a new disk...but that's another story.
> > > > > >
> > > > > > I have looked at what I can reduce to increase the space, and it seems one
> > > > > > of the smaller database I have on the server, i.e. 16MB, has a log file of
> > > > > > 4.8GB!
> > > > > >
> > > > > > I am not sure why this has occured, possibly due to not setting a checkpoint
> > > > > > during backup or something!?!?!
> > > > > >
> > > > > > ...anyway I am desperately tying to reduce the size of this file, and have
> > > > > > tried running the following:
> > > > > >
> > > > > > USE Comms
> > > > > > GO
> > > > > > DBCC SHRINKFILE (Comms_log, 10)
> > > > > >
> > > > > > and it just comes back and tells me:
> > > > > > - CurrentSize 608616
> > > > > > - Minimum Size 63
> > > > > > - UsedPages 608616
> > > > > > - EstimatedPages 56
> > > > > >
> > > > > > with no reduction in LogFile size.
> > > > > >
> > > > > > I also tried backing up just the databse and then restoring it under a
> > > > > > different name (so I can completely delete the old one and it's log!), but
> > > > > > when I try to restore it the system just comes back and tells me it hasn't
> > > > > > got enough space, even though it has just over 2GB (presumably it is trying,
> > > > > > or thinks it is trying to restore the log file as well, but goodness knows
> > > > > > where it's getting it from?!).
> > > > > >
> > > > > > Any ideas?
> > > > > >
> > > > > > Cheers, desperate Mike.
> > > > > >|||Be careful about restricting the size of the log files ... SQL Server may
need this disk space.
Instead I would recommend scheduling transaction log backups periodically,
minimum daily. This will allow SQL Server to reuse the disk space used by the
transaction log files.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Mike Owen" wrote:
> The answer to select databasepropertyex('my_db', 'recovery')
> is FULL.
> I tried the '...try the backup log with truncate_only and then shrink the
> log files' as you suggested and it WORKED!
> I am now back to 7GB of space, plenty of room for growth for all the DB's,
> and have restricted the size of all log files on the server.
> Thanks for all your help, you got me out of a scrape!
> Have a good weekend, cheers, Mike.
>
> "Ben Nevarez" wrote:
> >
> > Looks like you are not using the SIMPLE recovery model but try this
> >
> > select databasepropertyex('my_db', 'recovery')
> >
> > And also try the backup log with truncate_only and then shrink the log files.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Mike Owen" wrote:
> >
> > > Excuse my ignorance - It knows no bounds!
> > >
> > > How do I know if I am using the SIMPLE recovery model?
> > >
> > > I am quite happy to use: backup log my_db with truncate_only
> > >
> > > The database isn't used very much, and I only backed it up about 1 1/2 hours
> > > ago, so as long as I can recover that should something go wrong that would be
> > > fine....but perhaps that isn't possible with the backup I have?!!?
> > >
> > >
> > > Cheers, Mike.
> > >
> > > "Ben Nevarez" wrote:
> > >
> > > >
> > > > If you do not have enough disk space then use the TRUNCATE_ONLY option like
> > > > in
> > > >
> > > > backup log my_db with truncate_only
> > > >
> > > > We are supposing you are not running in the SIMPLE recovery model. Is this
> > > > correct?
> > > >
> > > > If that works, you must do a database backup at the end because by using
> > > > TRUNCATE_ONLY you are breaking the log chain.
> > > >
> > > > Hope this helps,
> > > >
> > > > Ben Nevarez
> > > > Senior Database Administrator
> > > > AIG SunAmerica
> > > >
> > > >
> > > >
> > > > "Mike Owen" wrote:
> > > >
> > > > > Thanks for you reply Ben, and you Chris. I don't think I can take a log file
> > > > > backup because I can oly do it on the local server and I haven't the space,
> > > > > i.e. 2GB space and 4GB+ log file.
> > > > >
> > > > > I have got a netwrok drive to a different server with space, but the SQL
> > > > > server backp program doesn't see netwrok drives.
> > > > >
> > > > > Any other ideas or a way I can backup to the network drive?
> > > > >
> > > > >
> > > > > Cheers, Mike.
> > > > >
> > > > > "Ben Nevarez" wrote:
> > > > >
> > > > > >
> > > > > > Hi Mike,
> > > > > >
> > > > > > Just do a transaction log backup and then try the shrink operation again.
> > > > > >
> > > > > > If that does not work check if you have transactions or jobs running for a
> > > > > > long time.
> > > > > >
> > > > > > Hope this helps,
> > > > > >
> > > > > > Ben Nevarez
> > > > > > Senior Database Administrator
> > > > > > AIG SunAmerica
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Mike Owen" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I have just come across a bit of a problem of my Windows 200 server running
> > > > > > > SQL Server 2000....I am running out of space!
> > > > > > >
> > > > > > > I have aout 2GB left. I have know this for some time, and for technical
> > > > > > > reasons just found out I can't add a new disk...but that's another story.
> > > > > > >
> > > > > > > I have looked at what I can reduce to increase the space, and it seems one
> > > > > > > of the smaller database I have on the server, i.e. 16MB, has a log file of
> > > > > > > 4.8GB!
> > > > > > >
> > > > > > > I am not sure why this has occured, possibly due to not setting a checkpoint
> > > > > > > during backup or something!?!?!
> > > > > > >
> > > > > > > ...anyway I am desperately tying to reduce the size of this file, and have
> > > > > > > tried running the following:
> > > > > > >
> > > > > > > USE Comms
> > > > > > > GO
> > > > > > > DBCC SHRINKFILE (Comms_log, 10)
> > > > > > >
> > > > > > > and it just comes back and tells me:
> > > > > > > - CurrentSize 608616
> > > > > > > - Minimum Size 63
> > > > > > > - UsedPages 608616
> > > > > > > - EstimatedPages 56
> > > > > > >
> > > > > > > with no reduction in LogFile size.
> > > > > > >
> > > > > > > I also tried backing up just the databse and then restoring it under a
> > > > > > > different name (so I can completely delete the old one and it's log!), but
> > > > > > > when I try to restore it the system just comes back and tells me it hasn't
> > > > > > > got enough space, even though it has just over 2GB (presumably it is trying,
> > > > > > > or thinks it is trying to restore the log file as well, but goodness knows
> > > > > > > where it's getting it from?!).
> > > > > > >
> > > > > > > Any ideas?
> > > > > > >
> > > > > > > Cheers, desperate Mike.
> > > > > > >|||You can also create an alert that will run the log backup if you fill it
over a certain percent. Providing that you do not have any very large
transactions this should keep you safe.
Chris
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:7BBF5A0F-45E0-4493-9A95-F50FF7D05D20@.microsoft.com...
> Be careful about restricting the size of the log files ... SQL Server may
> need this disk space.
> Instead I would recommend scheduling transaction log backups periodically,
> minimum daily. This will allow SQL Server to reuse the disk space used by
> the
> transaction log files.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Mike Owen" wrote:
>> The answer to select databasepropertyex('my_db', 'recovery')
>> is FULL.
>> I tried the '...try the backup log with truncate_only and then shrink
>> the
>> log files' as you suggested and it WORKED!
>> I am now back to 7GB of space, plenty of room for growth for all the
>> DB's,
>> and have restricted the size of all log files on the server.
>> Thanks for all your help, you got me out of a scrape!
>> Have a good weekend, cheers, Mike.
>>
>> "Ben Nevarez" wrote:
>> >
>> > Looks like you are not using the SIMPLE recovery model but try this
>> >
>> > select databasepropertyex('my_db', 'recovery')
>> >
>> > And also try the backup log with truncate_only and then shrink the log
>> > files.
>> >
>> > Hope this helps,
>> >
>> > Ben Nevarez
>> > Senior Database Administrator
>> > AIG SunAmerica
>> >
>> >
>> >
>> > "Mike Owen" wrote:
>> >
>> > > Excuse my ignorance - It knows no bounds!
>> > >
>> > > How do I know if I am using the SIMPLE recovery model?
>> > >
>> > > I am quite happy to use: backup log my_db with truncate_only
>> > >
>> > > The database isn't used very much, and I only backed it up about 1
>> > > 1/2 hours
>> > > ago, so as long as I can recover that should something go wrong that
>> > > would be
>> > > fine....but perhaps that isn't possible with the backup I have?!!?
>> > >
>> > >
>> > > Cheers, Mike.
>> > >
>> > > "Ben Nevarez" wrote:
>> > >
>> > > >
>> > > > If you do not have enough disk space then use the TRUNCATE_ONLY
>> > > > option like
>> > > > in
>> > > >
>> > > > backup log my_db with truncate_only
>> > > >
>> > > > We are supposing you are not running in the SIMPLE recovery model.
>> > > > Is this
>> > > > correct?
>> > > >
>> > > > If that works, you must do a database backup at the end because by
>> > > > using
>> > > > TRUNCATE_ONLY you are breaking the log chain.
>> > > >
>> > > > Hope this helps,
>> > > >
>> > > > Ben Nevarez
>> > > > Senior Database Administrator
>> > > > AIG SunAmerica
>> > > >
>> > > >
>> > > >
>> > > > "Mike Owen" wrote:
>> > > >
>> > > > > Thanks for you reply Ben, and you Chris. I don't think I can
>> > > > > take a log file
>> > > > > backup because I can oly do it on the local server and I haven't
>> > > > > the space,
>> > > > > i.e. 2GB space and 4GB+ log file.
>> > > > >
>> > > > > I have got a netwrok drive to a different server with space, but
>> > > > > the SQL
>> > > > > server backp program doesn't see netwrok drives.
>> > > > >
>> > > > > Any other ideas or a way I can backup to the network drive?
>> > > > >
>> > > > >
>> > > > > Cheers, Mike.
>> > > > >
>> > > > > "Ben Nevarez" wrote:
>> > > > >
>> > > > > >
>> > > > > > Hi Mike,
>> > > > > >
>> > > > > > Just do a transaction log backup and then try the shrink
>> > > > > > operation again.
>> > > > > >
>> > > > > > If that does not work check if you have transactions or jobs
>> > > > > > running for a
>> > > > > > long time.
>> > > > > >
>> > > > > > Hope this helps,
>> > > > > >
>> > > > > > Ben Nevarez
>> > > > > > Senior Database Administrator
>> > > > > > AIG SunAmerica
>> > > > > >
>> > > > > >
>> > > > > >
>> > > > > > "Mike Owen" wrote:
>> > > > > >
>> > > > > > > Hi,
>> > > > > > >
>> > > > > > > I have just come across a bit of a problem of my Windows 200
>> > > > > > > server running
>> > > > > > > SQL Server 2000....I am running out of space!
>> > > > > > >
>> > > > > > > I have aout 2GB left. I have know this for some time, and
>> > > > > > > for technical
>> > > > > > > reasons just found out I can't add a new disk...but that's
>> > > > > > > another story.
>> > > > > > >
>> > > > > > > I have looked at what I can reduce to increase the space, and
>> > > > > > > it seems one
>> > > > > > > of the smaller database I have on the server, i.e. 16MB, has
>> > > > > > > a log file of
>> > > > > > > 4.8GB!
>> > > > > > >
>> > > > > > > I am not sure why this has occured, possibly due to not
>> > > > > > > setting a checkpoint
>> > > > > > > during backup or something!?!?!
>> > > > > > >
>> > > > > > > ...anyway I am desperately tying to reduce the size of this
>> > > > > > > file, and have
>> > > > > > > tried running the following:
>> > > > > > >
>> > > > > > > USE Comms
>> > > > > > > GO
>> > > > > > > DBCC SHRINKFILE (Comms_log, 10)
>> > > > > > >
>> > > > > > > and it just comes back and tells me:
>> > > > > > > - CurrentSize 608616
>> > > > > > > - Minimum Size 63
>> > > > > > > - UsedPages 608616
>> > > > > > > - EstimatedPages 56
>> > > > > > >
>> > > > > > > with no reduction in LogFile size.
>> > > > > > >
>> > > > > > > I also tried backing up just the databse and then restoring
>> > > > > > > it under a
>> > > > > > > different name (so I can completely delete the old one and
>> > > > > > > it's log!), but
>> > > > > > > when I try to restore it the system just comes back and tells
>> > > > > > > me it hasn't
>> > > > > > > got enough space, even though it has just over 2GB
>> > > > > > > (presumably it is trying,
>> > > > > > > or thinks it is trying to restore the log file as well, but
>> > > > > > > goodness knows
>> > > > > > > where it's getting it from?!).
>> > > > > > >
>> > > > > > > Any ideas?
>> > > > > > >
>> > > > > > > Cheers, desperate Mike.
>> > > > > > >

Live server running out of space - Can't reduce Transaction Log!

Wednesday, March 7, 2012

List of columns from tables across databases.

Hey guys,

Couldn't find this anywhere in google.

I want a list of all database column names for a specific table/view
from across database.

I tried this...
----------------
Select *
>From Information_Schema.Columns
----------------

I also tried this...

----------------
select syscolumns.name, sysobjects.name, * from syscolumns, sysobjects
where
sysobjects.id = syscolumns.id
and (sysobjects.xtype='U' or sysobjects.xtype='S')
----------------

These queries return information about the CURRENT database.

But, if I want to do it ACROSS database or across servers.. how can I
do this?

I will express my gratitude to everyone who is kind enough to answer
this question. (I've been stuck with this problem for a while now.)

Thanks!

OhMyGaw!Query other databases using the three-part name:

SELECT *
FROM database_name.information_schema.columns

SELECT C.name, O.name, *
FROM database_name.dbo.syscolumns AS C,
database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

Assuming you have set up a linked server you can query other servers with
the four-part name:

SELECT *
FROM server_name.database_name.information_schema.colum ns

SELECT C.name, O.name, *
FROM server_name.database_name.dbo.syscolumns AS C,
server_name.database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

In each case the tables are distinct objects so if you want to combine
results from multiple databases either use a UNION or write a loop that
cycles through each DB. There is actually an undocumented proc that will
access each DB in turn:

EXEC sp_msforeachdb 'USE ? SELECT DB_NAME()'

This is something you should avoid in persistent code because it won't
necessarily be supported in future but it may help you if this is just a
one-off exercise.

--
David Portas
SQL Server MVP
--|||Query other databases using the three-part name:

SELECT *
FROM database_name.information_schema.columns

SELECT C.name, O.name, *
FROM database_name.dbo.syscolumns AS C,
database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

Assuming you have set up a linked server you can query other servers with
the four-part name:

SELECT *
FROM server_name.database_name.information_schema.colum ns

SELECT C.name, O.name, *
FROM server_name.database_name.dbo.syscolumns AS C,
server_name.database_name.dbo.sysobjects AS O
WHERE O.id = C.id
AND (O.xtype='U' OR O.xtype='S')

EXEC sp_msforeachdb 'USE ? SELECT DB_NAME()'

This is something you should avoid in persistent code because it won't
necessarily be supported in future but it may help you if this is just a
one-off exercise.

--
David Portas
SQL Server MVP
--|||David,

Thanks for your response. This is exactly what I was looking for.

SELECT *
FROM database_name.information_sche*ma.columns

I was trying the following

SELECT *
FROM database_name.database_owner.information_sche*ma.c olumns

BTW, where is this information_schema table? I couldn't find it when
I looked for it.

Thanks a bunch.|||David,

Thanks for your response. This is exactly what I was looking for.

SELECT *
FROM database_name.information_sche*ma.columns

I was trying the following

SELECT *
FROM database_name.database_owner.information_sche*ma.c olumns

BTW, where is this information_schema table? I couldn't find it when
I looked for it.

Thanks a bunch.|||Information_schema is a "schema" rather than a table. You can find the
definitions of the info schema views in Master.

In SQL Server 2000 "schema" is synonymous with "owner" and the
information_schema is implemented as a sort of virtual owner name that
points to the views in Master. SQL Server 2005 implements schemas
properly in a way that's consistent with other products and with the
SQL definition of the term.

--
David Portas
SQL Server MVP
--|||Information_schema is a "schema" rather than a table. You can find the
definitions of the info schema views in Master.

--
David Portas
SQL Server MVP
--

list of cities across the globe with time zones

Is there any managed website which maintains the list of
cities across the globe and their time zones in it.
Thanks,You might want to check out a company called Melissa Data. They sell all
sorts of data.
I've gotten a Phone Number to Zip code database from them, and I was very
pleased.
Michael
"DBA" <dbarchitec@.hotmail.com> wrote in message
news:87c901c4329f$f3ad8280$a401280a@.phx.gbl...
> Is there any managed website which maintains the list of
> cities across the globe and their time zones in it.
> Thanks,

list of cities across the globe with time zones

Is there any managed website which maintains the list of
cities across the globe and their time zones in it.
Thanks,
You might want to check out a company called Melissa Data. They sell all
sorts of data.
I've gotten a Phone Number to Zip code database from them, and I was very
pleased.
Michael
"DBA" <dbarchitec@.hotmail.com> wrote in message
news:87c901c4329f$f3ad8280$a401280a@.phx.gbl...
> Is there any managed website which maintains the list of
> cities across the globe and their time zones in it.
> Thanks,

Monday, March 26, 2012

Live server running out of space - Can't reduce Transaction Log!

Live server running out of space - Can't reduce Transaction Log!

Live server running out of space - Can't reduce Transaction Log!

Wednesday, March 7, 2012

List of columns from tables across databases.

list of cities across the globe with time zones

list of cities across the globe with time zones

List availables tables

Blog Archive

About Me