Database backups

When I backup one of the SQL server databases, the size of the backup file
varies from 1.2, 3.6 or 9.8MB. All these backups were made w/in 30-min of
each other with only me doing testing, no other users. This makes me feel a
bit uneasy, like I'm not sure if the backups are valid. Is this normal?
How can test a backup to make sure it's complete? I mean w/o deleting the
database and doing a restore.> When I backup one of the SQL server databases, the size of the backup file
> varies from 1.2, 3.6 or 9.8MB. All these backups were made w/in 30-min of
> each other with only me doing testing, no other users. This makes me feel
a
> bit uneasy, like I'm not sure if the backups are valid. Is this normal?
Did you append backups to the same file? Have you done always the same
backups or do you have full database, transaction log and differential
backups?
> How can test a backup to make sure it's complete? I mean w/o deleting the
> database and doing a restore.
You can always restore to a new database, so this is not a problem. Check
the "How to restore a database backup (Enterprise Manager)" topic in Books
OnLine.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.

Database backup size increases-no restore

Posted in MSDE no reply!Urgent
Our clients are using MSDE. Our VB application provides simple full backup
feature usng DMO. We have noticed that their recent backup size goes to 2GB.
to investigate, When backup taken from enterprise manager it is 60 MB. What
could be the rerasons?. The clients are doing hundered of inserts daily. but
the originam db size is not more than 100 MB.
When restored both backups it seems last 2 months of data is not there.
Need help? stuck
Abbas
Most probably you never overwrite the backup file, so you append all the time. This explain the
large size. Use RESTORE HEADERONLY to see what is on that backup. And when you restored, you
probably didn't specify a FILE option, which meant you restore the first one = the oldest one.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ABB" <ABB@.discussions.microsoft.com> wrote in message
news:53789646-1585-4F53-AF6A-0527CE037A65@.microsoft.com...
> Posted in MSDE no reply!Urgent
> Our clients are using MSDE. Our VB application provides simple full backup
> feature usng DMO. We have noticed that their recent backup size goes to 2GB.
> to investigate, When backup taken from enterprise manager it is 60 MB. What
> could be the rerasons?. The clients are doing hundered of inserts daily. but
> the originam db size is not more than 100 MB.
> When restored both backups it seems last 2 months of data is not there.
> Need help? stuck
> Abbas
>

Database backup size increases-no restore

Posted in MSDE no reply!Urgent
Our clients are using MSDE. Our VB application provides simple full backup
feature usng DMO. We have noticed that their recent backup size goes to 2GB.
to investigate, When backup taken from enterprise manager it is 60 MB. What
could be the rerasons?. The clients are doing hundered of inserts daily. but
the originam db size is not more than 100 MB.
When restored both backups it seems last 2 months of data is not there.
Need help? stuck
AbbasMost probably you never overwrite the backup file, so you append all the time. This explain the
large size. Use RESTORE HEADERONLY to see what is on that backup. And when you restored, you
probably didn't specify a FILE option, which meant you restore the first one = the oldest one.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ABB" <ABB@.discussions.microsoft.com> wrote in message
news:53789646-1585-4F53-AF6A-0527CE037A65@.microsoft.com...
> Posted in MSDE no reply!Urgent
> Our clients are using MSDE. Our VB application provides simple full backup
> feature usng DMO. We have noticed that their recent backup size goes to 2GB.
> to investigate, When backup taken from enterprise manager it is 60 MB. What
> could be the rerasons?. The clients are doing hundered of inserts daily. but
> the originam db size is not more than 100 MB.
> When restored both backups it seems last 2 months of data is not there.
> Need help? stuck
> Abbas
>

Database backup size increases-no restore

Posted in MSDE no reply!Urgent
Our clients are using MSDE. Our VB application provides simple full backup
feature usng DMO. We have noticed that their recent backup size goes to 2GB.
to investigate, When backup taken from enterprise manager it is 60 MB. What
could be the rerasons?. The clients are doing hundered of inserts daily. but
the originam db size is not more than 100 MB.
When restored both backups it seems last 2 months of data is not there.
Need help? stuck
AbbasMost probably you never overwrite the backup file, so you append all the tim
e. This explain the
large size. Use RESTORE HEADERONLY to see what is on that backup. And when y
ou restored, you
probably didn't specify a FILE option, which meant you restore the first one
= the oldest one.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ABB" <ABB@.discussions.microsoft.com> wrote in message
news:53789646-1585-4F53-AF6A-0527CE037A65@.microsoft.com...
> Posted in MSDE no reply!Urgent
> Our clients are using MSDE. Our VB application provides simple full backup
> feature usng DMO. We have noticed that their recent backup size goes to 2G
B.
> to investigate, When backup taken from enterprise manager it is 60 MB. Wha
t
> could be the rerasons?. The clients are doing hundered of inserts daily. b
ut
> the originam db size is not more than 100 MB.
> When restored both backups it seems last 2 months of data is not there.
> Need help? stuck
> Abbas
>

database backup file size

How does backup database command works? I don't see size of database backup file increasing while backup is in progress OR is it locked till the backup is finished.
Thanks.The size of the backup file (or files) is pre-calculated and the space is allocated before the actual backup starts. You will not see the size change while the backup is running.sql

Database Backup

Hi All,
We have SQL Server 2000. There were daily backup jobs scheduled and the file
size has grown very big 159 GB. How can we move this. Is there any way to
break this file and move. if we take fresh backup and delete this file will
there be any effect on the database. Backup and data are in the same drive.
Thank you in Advance.Kris
WINRAR,WINZIP to compress them?
"Kris" <Kris@.discussions.microsoft.com> wrote in message
news:561A8D62-B50E-4808-AA10-A63300829165@.microsoft.com...
> Hi All,
> We have SQL Server 2000. There were daily backup jobs scheduled and the
> file
> size has grown very big 159 GB. How can we move this. Is there any way to
> break this file and move. if we take fresh backup and delete this file
> will
> there be any effect on the database. Backup and data are in the same
> drive.
> Thank you in Advance.|||Hi Kris,
You can do a fresh backup without appending to that "big" file and if the
backup is OK (test by restoring) you can delete the 159 GB File. No,
removing an old backup has no effect on the database itself [unless you
lose the database and you desperately need that backup :)]
Nathan H. Omukwenyi
"Kris" <Kris@.discussions.microsoft.com> wrote in message
news:561A8D62-B50E-4808-AA10-A63300829165@.microsoft.com...
> Hi All,
> We have SQL Server 2000. There were daily backup jobs scheduled and the
> file
> size has grown very big 159 GB. How can we move this. Is there any way to
> break this file and move. if we take fresh backup and delete this file
> will
> there be any effect on the database. Backup and data are in the same
> drive.
> Thank you in Advance.|||Hi Nathan,
At present i do not have any disk space in this drive where SQL is also
there can i do the backup in other drive will there be any problem. The
previous administrator should't have taken daily backup in the same drive.
Now i am just sorting out this issue. Should i take the backup of all the
databses and master then delete this will there be any critical issues. Any
precautions during this process or did i miss any thing else to do backup.
Thank you once again
"Nathan H. Omukwenyi" wrote:
> Hi Kris,
> You can do a fresh backup without appending to that "big" file and if the
> backup is OK (test by restoring) you can delete the 159 GB File. No,
> removing an old backup has no effect on the database itself [unless you
> lose the database and you desperately need that backup :)]
> Nathan H. Omukwenyi
> "Kris" <Kris@.discussions.microsoft.com> wrote in message
> news:561A8D62-B50E-4808-AA10-A63300829165@.microsoft.com...
> > Hi All,
> > We have SQL Server 2000. There were daily backup jobs scheduled and the
> > file
> > size has grown very big 159 GB. How can we move this. Is there any way to
> > break this file and move. if we take fresh backup and delete this file
> > will
> > there be any effect on the database. Backup and data are in the same
> > drive.
> >
> > Thank you in Advance.
>
>|||Best way is to backup all your databases except tempdb to another drive.
There will be no problem for SQL-Server. make sure you make a full back-up
But before you delete the old backup files why don't you put them on a tape
"Kris" wrote:
> Hi Nathan,
> At present i do not have any disk space in this drive where SQL is also
> there can i do the backup in other drive will there be any problem. The
> previous administrator should't have taken daily backup in the same drive.
> Now i am just sorting out this issue. Should i take the backup of all the
> databses and master then delete this will there be any critical issues. Any
> precautions during this process or did i miss any thing else to do backup.
> Thank you once again
>
> "Nathan H. Omukwenyi" wrote:
> > Hi Kris,
> >
> > You can do a fresh backup without appending to that "big" file and if the
> > backup is OK (test by restoring) you can delete the 159 GB File. No,
> > removing an old backup has no effect on the database itself [unless you
> > lose the database and you desperately need that backup :)]
> >
> > Nathan H. Omukwenyi
> >
> > "Kris" <Kris@.discussions.microsoft.com> wrote in message
> > news:561A8D62-B50E-4808-AA10-A63300829165@.microsoft.com...
> > > Hi All,
> > > We have SQL Server 2000. There were daily backup jobs scheduled and the
> > > file
> > > size has grown very big 159 GB. How can we move this. Is there any way to
> > > break this file and move. if we take fresh backup and delete this file
> > > will
> > > there be any effect on the database. Backup and data are in the same
> > > drive.
> > >
> > > Thank you in Advance.
> >
> >
> >|||Right now take a backup on shared drive.
Then delete the 159gb backup file on your drive.
In future do not put backup data on the same drive, or copy backup
daily to tape from drive.
This is required because if disk will fail you will lose both data and
backup.
One more thing do not append backup on same file. In stead keep backup
policy to have different file for each backup and maintain 2-3 days
backup on drive and delete old backup.
In tape we keep backup of last 7 days, every sunday backup of last four
week and a bakcup of last month. This is the policy which we follows.
Regards
Amish Shah|||Zekske
Actually there is no tape device with this server the previous admin person
just created a folder in the SQL Server and scheduled backup jobs in the
same place where data is stored daily.
Now i have paused all backup scheduled jobs. The left out space now is 350
MB will it crash and we are working on a solution. How we can move this to
other server which has more space but it is SUN.
Just working on.. any advise , Thank you once again guys.
"Zekske" wrote:
> Best way is to backup all your databases except tempdb to another drive.
> There will be no problem for SQL-Server. make sure you make a full back-up
> But before you delete the old backup files why don't you put them on a tape
> "Kris" wrote:
> > Hi Nathan,
> > At present i do not have any disk space in this drive where SQL is also
> > there can i do the backup in other drive will there be any problem. The
> > previous administrator should't have taken daily backup in the same drive.
> > Now i am just sorting out this issue. Should i take the backup of all the
> > databses and master then delete this will there be any critical issues. Any
> > precautions during this process or did i miss any thing else to do backup.
> >
> > Thank you once again
> >
> >
> > "Nathan H. Omukwenyi" wrote:
> >
> > > Hi Kris,
> > >
> > > You can do a fresh backup without appending to that "big" file and if the
> > > backup is OK (test by restoring) you can delete the 159 GB File. No,
> > > removing an old backup has no effect on the database itself [unless you
> > > lose the database and you desperately need that backup :)]
> > >
> > > Nathan H. Omukwenyi
> > >
> > > "Kris" <Kris@.discussions.microsoft.com> wrote in message
> > > news:561A8D62-B50E-4808-AA10-A63300829165@.microsoft.com...
> > > > Hi All,
> > > > We have SQL Server 2000. There were daily backup jobs scheduled and the
> > > > file
> > > > size has grown very big 159 GB. How can we move this. Is there any way to
> > > > break this file and move. if we take fresh backup and delete this file
> > > > will
> > > > there be any effect on the database. Backup and data are in the same
> > > > drive.
> > > >
> > > > Thank you in Advance.
> > >
> > >
> > >|||Amish
At present we do not have such a big disk space. And we do not have tape
drive attached. Prior to this it was backedup in the same drive. We have Sun
unix systems which has good disk space and also backup tape drives we are
aksing our unix guys. Do you know any way we can move or copy this files.
Thank you..
"amish" wrote:
> Right now take a backup on shared drive.
> Then delete the 159gb backup file on your drive.
> In future do not put backup data on the same drive, or copy backup
> daily to tape from drive.
> This is required because if disk will fail you will lose both data and
> backup.
> One more thing do not append backup on same file. In stead keep backup
> policy to have different file for each backup and maintain 2-3 days
> backup on drive and delete old backup.
> In tape we keep backup of last 7 days, every sunday backup of last four
> week and a bakcup of last month. This is the policy which we follows.
> Regards
> Amish Shah
>|||"Kris" <Kris@.discussions.microsoft.com> wrote in message
news:2A7AC679-2081-44D1-966A-A70395C2124C@.microsoft.com...
> Amish
> At present we do not have such a big disk space. And we do not have tape
> drive attached. Prior to this it was backedup in the same drive. We have
Sun
> unix systems which has good disk space and also backup tape drives we are
> aksing our unix guys. Do you know any way we can move or copy this files.
>
If you can run a version of Samba on the SUN system, you can create a shared
drive and backup to that.
I would do what others suggested. Make a full backup to the shared drive
and then delete the 159gb drive.
I would also suggest taht you setup the backups to go to a DIFFERENT file
each time (and then deleting the oldest files).
For example, 7 scripts, one for each day of the week that backups to
\\remoteSunshare\Monday, Tuesday, etc.
Or use the maintenance wizard and setup on a daily (or however often) one to
back up and keep N copies around.
> Thank you..
> "amish" wrote:
> > Right now take a backup on shared drive.
> > Then delete the 159gb backup file on your drive.
> >
> > In future do not put backup data on the same drive, or copy backup
> > daily to tape from drive.
> > This is required because if disk will fail you will lose both data and
> > backup.
> >
> > One more thing do not append backup on same file. In stead keep backup
> > policy to have different file for each backup and maintain 2-3 days
> > backup on drive and delete old backup.
> >
> > In tape we keep backup of last 7 days, every sunday backup of last four
> > week and a bakcup of last month. This is the policy which we follows.
> >
> > Regards
> > Amish Shah
> >
> >|||Dear Greg & All
Thank you once again, I shall do as suggested.
Keep up the good work
Regards
"Greg D. Moore (Strider)" wrote:
> "Kris" <Kris@.discussions.microsoft.com> wrote in message
> news:2A7AC679-2081-44D1-966A-A70395C2124C@.microsoft.com...
> > Amish
> > At present we do not have such a big disk space. And we do not have tape
> > drive attached. Prior to this it was backedup in the same drive. We have
> Sun
> > unix systems which has good disk space and also backup tape drives we are
> > aksing our unix guys. Do you know any way we can move or copy this files.
> >
> If you can run a version of Samba on the SUN system, you can create a shared
> drive and backup to that.
> I would do what others suggested. Make a full backup to the shared drive
> and then delete the 159gb drive.
> I would also suggest taht you setup the backups to go to a DIFFERENT file
> each time (and then deleting the oldest files).
> For example, 7 scripts, one for each day of the week that backups to
> \\remoteSunshare\Monday, Tuesday, etc.
> Or use the maintenance wizard and setup on a daily (or however often) one to
> back up and keep N copies around.
>
> > Thank you..
> >
> > "amish" wrote:
> >
> > > Right now take a backup on shared drive.
> > > Then delete the 159gb backup file on your drive.
> > >
> > > In future do not put backup data on the same drive, or copy backup
> > > daily to tape from drive.
> > > This is required because if disk will fail you will lose both data and
> > > backup.
> > >
> > > One more thing do not append backup on same file. In stead keep backup
> > > policy to have different file for each backup and maintain 2-3 days
> > > backup on drive and delete old backup.
> > >
> > > In tape we keep backup of last 7 days, every sunday backup of last four
> > > week and a bakcup of last month. This is the policy which we follows.
> > >
> > > Regards
> > > Amish Shah
> > >
> > >
>
>

Database auto-recovery after server startup

Hi All,
There is a relative-large Database (>12GB in size) in my SQLServer. It
can run as usual without restarting pc machine. After reboot the machine,
the large database would automatically recover for a long time (more than 5
hours). What should the problem be and what can i do for it? Please give
some advice. Thank you !Hi,
As a initial step,
Before rebooting the server please stop the SQL Server service and once it
is sucessful you can restart the Machine.
The only issue I can forsee is " The machine should have been rebooted while
a huge batch process is going on". Due to this after reboot
the Rollback and Roll forward will happen and during this time the database
will be in recovaring status.
Thanks
Hari
MCDBA
"Derek" <dereklim@.southasiagroup.com.hk> wrote in message
news:#wlYpfO1DHA.1272@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> There is a relative-large Database (>12GB in size) in my SQLServer. It
> can run as usual without restarting pc machine. After reboot the machine,
> the large database would automatically recover for a long time (more than
5
> hours). What should the problem be and what can i do for it? Please give
> some advice. Thank you !
>|||Also check the sp_configure recovery interval in minutes,,, it probably
needs to be 0, or < 5.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Derek" <dereklim@.southasiagroup.com.hk> wrote in message
news:%23wlYpfO1DHA.1272@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> There is a relative-large Database (>12GB in size) in my SQLServer. It
> can run as usual without restarting pc machine. After reboot the machine,
> the large database would automatically recover for a long time (more than
5
> hours). What should the problem be and what can i do for it? Please give
> some advice. Thank you !
>

database attachement

I'm unable to attach a database in sql server which was detached previously
by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
Whenever I tried to attached the database the system goed hang.
Have you ensured that .mdb and .ldf files are available at the logical file
location path?
And do you see any error messages in errorlogs?
Thanks
GYK
"Paramjeet" wrote:

> I'm unable to attach a database in sql server which was detached previously
> by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
> Whenever I tried to attached the database the system goed hang.
>
|||You might try this techniquue:
Create a new databse with the same name and the same name for the data and
log files.
Stop SQL Server
Copy your database mdf and ldf files over the top of the new created files.
This should allow the database to be seen by SQL Server.
At this point I would run DBCC Checkdb to make sure there is no structural
problems with the database.
Rand
This posting is provided "as is" with no warranties and confers no rights.

database attachement

I'm unable to attach a database in sql server which was detached previously
by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
Whenever I tried to attached the database the system goed hang.Have you ensured that .mdb and .ldf files are available at the logical file
location path?
And do you see any error messages in errorlogs?
Thanks
GYK
"Paramjeet" wrote:
> I'm unable to attach a database in sql server which was detached previously
> by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
> Whenever I tried to attached the database the system goed hang.
>|||You might try this techniquue:
Create a new databse with the same name and the same name for the data and
log files.
Stop SQL Server
Copy your database mdf and ldf files over the top of the new created files.
This should allow the database to be seen by SQL Server.
At this point I would run DBCC Checkdb to make sure there is no structural
problems with the database.
Rand
This posting is provided "as is" with no warranties and confers no rights.

database attachement

I'm unable to attach a database in sql server which was detached previously
by me. The size of the mdb file is 12 gb and size of the log file is 20 gb.
Whenever I tried to attached the database the system goed hang.Have you ensured that .mdb and .ldf files are available at the logical file
location path?
And do you see any error messages in errorlogs?
Thanks
GYK
"Paramjeet" wrote:

> I'm unable to attach a database in sql server which was detached previousl
y
> by me. The size of the mdb file is 12 gb and size of the log file is 20 gb
.
> Whenever I tried to attached the database the system goed hang.
>|||You might try this techniquue:
Create a new databse with the same name and the same name for the data and
log files.
Stop SQL Server
Copy your database mdf and ldf files over the top of the new created files.
This should allow the database to be seen by SQL Server.
At this point I would run DBCC Checkdb to make sure there is no structural
problems with the database.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Database Archive Data

I have a production database thats approximately 50 GB in
size with SQL Server 2000 Enterprise Edition. I truncate
the non-essential tables nightly. Does Microsoft
recommend partitioning tables for core active data in the
tables thats access by the user (Active), the other part
of the partition as data that is presently not used by
the application (Non-Active).
What archiving technique does Microsoft recommend?
Thank You,
MikeThere is no general rule of thumb or official recommendation for things like
this. There are so many things that are app or design specific that can
affect this decision. The choice to partition / archive / truncate etc. is
really dependant on what you need to do with this data. If you almost never
have to access it again then it is usually not a bad idea to archive it to
files or another database. If your current operation and hardware can
support it either way, you may choose to just leave it where it is. 50GB is
not that large of a database these days and the sheer size alone is really
no indication that you should do one thing over another.
--
Andrew J. Kelly SQL MVP
"MIke" <anonymous@.discussions.microsoft.com> wrote in message
news:67aa01c482d0$c6706680$a601280a@.phx.gbl...
> I have a production database thats approximately 50 GB in
> size with SQL Server 2000 Enterprise Edition. I truncate
> the non-essential tables nightly. Does Microsoft
> recommend partitioning tables for core active data in the
> tables thats access by the user (Active), the other part
> of the partition as data that is presently not used by
> the application (Non-Active).
> What archiving technique does Microsoft recommend?
> Thank You,
> Mike

Database Architecture for keep tera byte of data

Hi

we are keeping five yeare of data which can be gone up to terabyte in size . when we are retreaving the data it will take more time to view the report .please send us the database architectur or way of keeping data to speeded up the reporting.

We have indexed the table.

Thanks

Pubudu

I recommend the Fast Terrabyte Database Architecture (patent applied for but declined) methodology. This architecture is known to be the fastest in the world. I have witnessed retrieval times faster than selecting a single record from a row of 5!! Also this method requires no indexing what-so-ever.

Unfortunately, the secret to this architecture died with it's inventor, C.J. Time. So I now recommend the Database Tuning Advisor. As for architecture, if you could provide a few more details, I'll bet some better advice would be forthcoming.

|||

Hi

I can give u a details about the database.

Thats having 25 tables.It uses for Point of Sales (pos) system.Data is comming from more than 100 branches.Therefore there can be

more than 10000 records for a day per outlet.From those all outlets data is comming to the head office database.So we are taking the

reports using centraldata base management desision making purposes.

Thanks

Pubudu

|||

10,000 records per day per outlet x 100 outlets is 1 million records per day x 220 business days per year x 5 years is ~ a billion records.

My approach would be to roll up into a data warehouse daily for analysis.

Database and log file size

I've been reading SQL Server High Availability and went thru some recent
webcasts by Kimberly Tripp and from what I've found out, I think we need to
do some adjustments to our database. It is SQL Server 2000 back end for an
ecommerce site. When it was set up, we just went with default options, but
now we need to do some changing. When I did dbcc loginfo, it came back with
275 vlfs. The database size after about a year and a half is 3.9 GB, it says
space available 1.4 GB.
Space Allocated is
Data File 2.4 gb
Transaction log 1.4 GB
It is running in full recovery mode. Transaction logs are backed up every 2
hours. Database is backed up once a day.
I'm afraid that some of these properties are causing slowdowns and deadlocks.
What would be a reasonable size to set the db and transaction log to at this
growth rate? And grow it by what % or MB? I don't really know what to even
start with and I don't want to do something that will make performance worse.
Thanks for any help.
--
JudyJudy,
These settings basically look fine and are probably not associated with
slowdowns and especially not deadlocks. You should always have plenty of
free space in the data and log files. It sounds like the log file is fine
but I can't tell from that how much free space you have in the datafile.
Right click on the database node in EM and choose "view - Task Pad" to see
the breakdown for each file.The growth should be a fixed size in MB. The
size should be small enough so that it can grow in about 10 seconds or less
but not too small that it would grow over and over.
--
Andrew J. Kelly SQL MVP
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
> I've been reading SQL Server High Availability and went thru some recent
> webcasts by Kimberly Tripp and from what I've found out, I think we need
> to
> do some adjustments to our database. It is SQL Server 2000 back end for an
> ecommerce site. When it was set up, we just went with default options, but
> now we need to do some changing. When I did dbcc loginfo, it came back
> with
> 275 vlfs. The database size after about a year and a half is 3.9 GB, it
> says
> space available 1.4 GB.
> Space Allocated is
> Data File 2.4 gb
> Transaction log 1.4 GB
> It is running in full recovery mode. Transaction logs are backed up every
> 2
> hours. Database is backed up once a day.
> I'm afraid that some of these properties are causing slowdowns and
> deadlocks.
>
> What would be a reasonable size to set the db and transaction log to at
> this
> growth rate? And grow it by what % or MB? I don't really know what to even
> start with and I don't want to do something that will make performance
> worse.
> Thanks for any help.
> --
> Judy|||The database shows 2438.75MB allocated with 2372.06MB used.
The transaction log shows .1441.3 MB allocated with 104.9 used after a log
backup an hour and 1/2 previously.
The growth is fixed at 100MB for both database and log files.
--
Judy
"Andrew J. Kelly" wrote:
> Judy,
> These settings basically look fine and are probably not associated with
> slowdowns and especially not deadlocks. You should always have plenty of
> free space in the data and log files. It sounds like the log file is fine
> but I can't tell from that how much free space you have in the datafile.
> Right click on the database node in EM and choose "view - Task Pad" to see
> the breakdown for each file.The growth should be a fixed size in MB. The
> size should be small enough so that it can grow in about 10 seconds or less
> but not too small that it would grow over and over.
> --
> Andrew J. Kelly SQL MVP
>
> "Judy" <Judy@.discussions.microsoft.com> wrote in message
> news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
> > I've been reading SQL Server High Availability and went thru some recent
> > webcasts by Kimberly Tripp and from what I've found out, I think we need
> > to
> > do some adjustments to our database. It is SQL Server 2000 back end for an
> > ecommerce site. When it was set up, we just went with default options, but
> > now we need to do some changing. When I did dbcc loginfo, it came back
> > with
> > 275 vlfs. The database size after about a year and a half is 3.9 GB, it
> > says
> > space available 1.4 GB.
> > Space Allocated is
> > Data File 2.4 gb
> > Transaction log 1.4 GB
> >
> > It is running in full recovery mode. Transaction logs are backed up every
> > 2
> > hours. Database is backed up once a day.
> >
> > I'm afraid that some of these properties are causing slowdowns and
> > deadlocks.
> >
> >
> > What would be a reasonable size to set the db and transaction log to at
> > this
> > growth rate? And grow it by what % or MB? I don't really know what to even
> > start with and I don't want to do something that will make performance
> > worse.
> >
> > Thanks for any help.
> > --
> > Judy
>
>|||You will want more free space in the data file. The log seems fine.
--
Andrew J. Kelly SQL MVP
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:0EF8EC72-769A-4304-8107-36ECB56B465B@.microsoft.com...
> The database shows 2438.75MB allocated with 2372.06MB used.
> The transaction log shows .1441.3 MB allocated with 104.9 used after a log
> backup an hour and 1/2 previously.
> The growth is fixed at 100MB for both database and log files.
> --
> Judy
>
> "Andrew J. Kelly" wrote:
>> Judy,
>> These settings basically look fine and are probably not associated with
>> slowdowns and especially not deadlocks. You should always have plenty of
>> free space in the data and log files. It sounds like the log file is fine
>> but I can't tell from that how much free space you have in the datafile.
>> Right click on the database node in EM and choose "view - Task Pad" to
>> see
>> the breakdown for each file.The growth should be a fixed size in MB. The
>> size should be small enough so that it can grow in about 10 seconds or
>> less
>> but not too small that it would grow over and over.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Judy" <Judy@.discussions.microsoft.com> wrote in message
>> news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
>> > I've been reading SQL Server High Availability and went thru some
>> > recent
>> > webcasts by Kimberly Tripp and from what I've found out, I think we
>> > need
>> > to
>> > do some adjustments to our database. It is SQL Server 2000 back end for
>> > an
>> > ecommerce site. When it was set up, we just went with default options,
>> > but
>> > now we need to do some changing. When I did dbcc loginfo, it came back
>> > with
>> > 275 vlfs. The database size after about a year and a half is 3.9 GB, it
>> > says
>> > space available 1.4 GB.
>> > Space Allocated is
>> > Data File 2.4 gb
>> > Transaction log 1.4 GB
>> >
>> > It is running in full recovery mode. Transaction logs are backed up
>> > every
>> > 2
>> > hours. Database is backed up once a day.
>> >
>> > I'm afraid that some of these properties are causing slowdowns and
>> > deadlocks.
>> >
>> >
>> > What would be a reasonable size to set the db and transaction log to at
>> > this
>> > growth rate? And grow it by what % or MB? I don't really know what to
>> > even
>> > start with and I don't want to do something that will make performance
>> > worse.
>> >
>> > Thanks for any help.
>> > --
>> > Judy
>>|||The idea log size is the smallest that you can make it without it having to
grow before you back it up. If the transaction log really reaches 1.4GB used
before a backup, then that is your ideal size - well...maybe a little bit of
room to spare...like perhaps 2GB.
So to follow Kimberly's suggestion, backup the transcaction log, shrink it,
and then set it's size back to 1.4GB. The idea is that having to grow the
log is what you want to avoid.
As for the database file grow amounts, i personally like the 20% grow
amount. Every time the database grows, 20% allocates a lot of space (A 2MB
file growing by 20% is a lot less space added compared to a 2GB file growing
by 20%)
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
> I've been reading SQL Server High Availability and went thru some recent
> webcasts by Kimberly Tripp and from what I've found out, I think we need
> to
> do some adjustments to our database. It is SQL Server 2000 back end for an
> ecommerce site. When it was set up, we just went with default options, but
> now we need to do some changing. When I did dbcc loginfo, it came back
> with
> 275 vlfs. The database size after about a year and a half is 3.9 GB, it
> says
> space available 1.4 GB.
> Space Allocated is
> Data File 2.4 gb
> Transaction log 1.4 GB
> It is running in full recovery mode. Transaction logs are backed up every
> 2
> hours. Database is backed up once a day.
> I'm afraid that some of these properties are causing slowdowns and
> deadlocks.
>
> What would be a reasonable size to set the db and transaction log to at
> this
> growth rate? And grow it by what % or MB? I don't really know what to even
> start with and I don't want to do something that will make performance
> worse.
> Thanks for any help.
> --
> Judy

Database and log file size

I've been reading SQL Server High Availability and went thru some recent
webcasts by Kimberly Tripp and from what I've found out, I think we need to
do some adjustments to our database. It is SQL Server 2000 back end for an
ecommerce site. When it was set up, we just went with default options, but
now we need to do some changing. When I did dbcc loginfo, it came back with
275 vlfs. The database size after about a year and a half is 3.9 GB, it says
space available 1.4 GB.
Space Allocated is
Data File 2.4 gb
Transaction log 1.4 GB
It is running in full recovery mode. Transaction logs are backed up every 2
hours. Database is backed up once a day.
I'm afraid that some of these properties are causing slowdowns and deadlocks.
What would be a reasonable size to set the db and transaction log to at this
growth rate? And grow it by what % or MB? I don't really know what to even
start with and I don't want to do something that will make performance worse.
Thanks for any help.
Judy
Judy,
These settings basically look fine and are probably not associated with
slowdowns and especially not deadlocks. You should always have plenty of
free space in the data and log files. It sounds like the log file is fine
but I can't tell from that how much free space you have in the datafile.
Right click on the database node in EM and choose "view - Task Pad" to see
the breakdown for each file.The growth should be a fixed size in MB. The
size should be small enough so that it can grow in about 10 seconds or less
but not too small that it would grow over and over.
Andrew J. Kelly SQL MVP
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
> I've been reading SQL Server High Availability and went thru some recent
> webcasts by Kimberly Tripp and from what I've found out, I think we need
> to
> do some adjustments to our database. It is SQL Server 2000 back end for an
> ecommerce site. When it was set up, we just went with default options, but
> now we need to do some changing. When I did dbcc loginfo, it came back
> with
> 275 vlfs. The database size after about a year and a half is 3.9 GB, it
> says
> space available 1.4 GB.
> Space Allocated is
> Data File 2.4 gb
> Transaction log 1.4 GB
> It is running in full recovery mode. Transaction logs are backed up every
> 2
> hours. Database is backed up once a day.
> I'm afraid that some of these properties are causing slowdowns and
> deadlocks.
>
> What would be a reasonable size to set the db and transaction log to at
> this
> growth rate? And grow it by what % or MB? I don't really know what to even
> start with and I don't want to do something that will make performance
> worse.
> Thanks for any help.
> --
> Judy
|||The database shows 2438.75MB allocated with 2372.06MB used.
The transaction log shows .1441.3 MB allocated with 104.9 used after a log
backup an hour and 1/2 previously.
The growth is fixed at 100MB for both database and log files.
Judy
"Andrew J. Kelly" wrote:

> Judy,
> These settings basically look fine and are probably not associated with
> slowdowns and especially not deadlocks. You should always have plenty of
> free space in the data and log files. It sounds like the log file is fine
> but I can't tell from that how much free space you have in the datafile.
> Right click on the database node in EM and choose "view - Task Pad" to see
> the breakdown for each file.The growth should be a fixed size in MB. The
> size should be small enough so that it can grow in about 10 seconds or less
> but not too small that it would grow over and over.
> --
> Andrew J. Kelly SQL MVP
>
> "Judy" <Judy@.discussions.microsoft.com> wrote in message
> news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
>
>
|||You will want more free space in the data file. The log seems fine.
Andrew J. Kelly SQL MVP
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:0EF8EC72-769A-4304-8107-36ECB56B465B@.microsoft.com...[vbcol=seagreen]
> The database shows 2438.75MB allocated with 2372.06MB used.
> The transaction log shows .1441.3 MB allocated with 104.9 used after a log
> backup an hour and 1/2 previously.
> The growth is fixed at 100MB for both database and log files.
> --
> Judy
>
> "Andrew J. Kelly" wrote:
|||The idea log size is the smallest that you can make it without it having to
grow before you back it up. If the transaction log really reaches 1.4GB used
before a backup, then that is your ideal size - well...maybe a little bit of
room to spare...like perhaps 2GB.
So to follow Kimberly's suggestion, backup the transcaction log, shrink it,
and then set it's size back to 1.4GB. The idea is that having to grow the
log is what you want to avoid.
As for the database file grow amounts, i personally like the 20% grow
amount. Every time the database grows, 20% allocates a lot of space (A 2MB
file growing by 20% is a lot less space added compared to a 2GB file growing
by 20%)
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
> I've been reading SQL Server High Availability and went thru some recent
> webcasts by Kimberly Tripp and from what I've found out, I think we need
> to
> do some adjustments to our database. It is SQL Server 2000 back end for an
> ecommerce site. When it was set up, we just went with default options, but
> now we need to do some changing. When I did dbcc loginfo, it came back
> with
> 275 vlfs. The database size after about a year and a half is 3.9 GB, it
> says
> space available 1.4 GB.
> Space Allocated is
> Data File 2.4 gb
> Transaction log 1.4 GB
> It is running in full recovery mode. Transaction logs are backed up every
> 2
> hours. Database is backed up once a day.
> I'm afraid that some of these properties are causing slowdowns and
> deadlocks.
>
> What would be a reasonable size to set the db and transaction log to at
> this
> growth rate? And grow it by what % or MB? I don't really know what to even
> start with and I don't want to do something that will make performance
> worse.
> Thanks for any help.
> --
> Judy

Database and log file size

I've been reading SQL Server High Availability and went thru some recent
webcasts by Kimberly Tripp and from what I've found out, I think we need to
do some adjustments to our database. It is SQL Server 2000 back end for an
ecommerce site. When it was set up, we just went with default options, but
now we need to do some changing. When I did dbcc loginfo, it came back with
275 vlfs. The database size after about a year and a half is 3.9 GB, it says
space available 1.4 GB.
Space Allocated is
Data File 2.4 gb
Transaction log 1.4 GB
It is running in full recovery mode. Transaction logs are backed up every 2
hours. Database is backed up once a day.
I'm afraid that some of these properties are causing slowdowns and deadlocks
.
What would be a reasonable size to set the db and transaction log to at this
growth rate? And grow it by what % or MB? I don't really know what to even
start with and I don't want to do something that will make performance worse
.
Thanks for any help.
--
JudyJudy,
These settings basically look fine and are probably not associated with
slowdowns and especially not deadlocks. You should always have plenty of
free space in the data and log files. It sounds like the log file is fine
but I can't tell from that how much free space you have in the datafile.
Right click on the database node in EM and choose "view - Task Pad" to see
the breakdown for each file.The growth should be a fixed size in MB. The
size should be small enough so that it can grow in about 10 seconds or less
but not too small that it would grow over and over.
Andrew J. Kelly SQL MVP
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
> I've been reading SQL Server High Availability and went thru some recent
> webcasts by Kimberly Tripp and from what I've found out, I think we need
> to
> do some adjustments to our database. It is SQL Server 2000 back end for an
> ecommerce site. When it was set up, we just went with default options, but
> now we need to do some changing. When I did dbcc loginfo, it came back
> with
> 275 vlfs. The database size after about a year and a half is 3.9 GB, it
> says
> space available 1.4 GB.
> Space Allocated is
> Data File 2.4 gb
> Transaction log 1.4 GB
> It is running in full recovery mode. Transaction logs are backed up every
> 2
> hours. Database is backed up once a day.
> I'm afraid that some of these properties are causing slowdowns and
> deadlocks.
>
> What would be a reasonable size to set the db and transaction log to at
> this
> growth rate? And grow it by what % or MB? I don't really know what to even
> start with and I don't want to do something that will make performance
> worse.
> Thanks for any help.
> --
> Judy|||The database shows 2438.75MB allocated with 2372.06MB used.
The transaction log shows .1441.3 MB allocated with 104.9 used after a log
backup an hour and 1/2 previously.
The growth is fixed at 100MB for both database and log files.
--
Judy
"Andrew J. Kelly" wrote:

> Judy,
> These settings basically look fine and are probably not associated with
> slowdowns and especially not deadlocks. You should always have plenty of
> free space in the data and log files. It sounds like the log file is fine
> but I can't tell from that how much free space you have in the datafile.
> Right click on the database node in EM and choose "view - Task Pad" to see
> the breakdown for each file.The growth should be a fixed size in MB. The
> size should be small enough so that it can grow in about 10 seconds or les
s
> but not too small that it would grow over and over.
> --
> Andrew J. Kelly SQL MVP
>
> "Judy" <Judy@.discussions.microsoft.com> wrote in message
> news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
>
>|||You will want more free space in the data file. The log seems fine.
Andrew J. Kelly SQL MVP
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:0EF8EC72-769A-4304-8107-36ECB56B465B@.microsoft.com...[vbcol=seagreen]
> The database shows 2438.75MB allocated with 2372.06MB used.
> The transaction log shows .1441.3 MB allocated with 104.9 used after a log
> backup an hour and 1/2 previously.
> The growth is fixed at 100MB for both database and log files.
> --
> Judy
>
> "Andrew J. Kelly" wrote:
>|||The idea log size is the smallest that you can make it without it having to
grow before you back it up. If the transaction log really reaches 1.4GB used
before a backup, then that is your ideal size - well...maybe a little bit of
room to spare...like perhaps 2GB.
So to follow Kimberly's suggestion, backup the transcaction log, shrink it,
and then set it's size back to 1.4GB. The idea is that having to grow the
log is what you want to avoid.
As for the database file grow amounts, i personally like the 20% grow
amount. Every time the database grows, 20% allocates a lot of space (A 2MB
file growing by 20% is a lot less space added compared to a 2GB file growing
by 20%)
"Judy" <Judy@.discussions.microsoft.com> wrote in message
news:950D2A43-F458-438F-8248-3225648CBE1F@.microsoft.com...
> I've been reading SQL Server High Availability and went thru some recent
> webcasts by Kimberly Tripp and from what I've found out, I think we need
> to
> do some adjustments to our database. It is SQL Server 2000 back end for an
> ecommerce site. When it was set up, we just went with default options, but
> now we need to do some changing. When I did dbcc loginfo, it came back
> with
> 275 vlfs. The database size after about a year and a half is 3.9 GB, it
> says
> space available 1.4 GB.
> Space Allocated is
> Data File 2.4 gb
> Transaction log 1.4 GB
> It is running in full recovery mode. Transaction logs are backed up every
> 2
> hours. Database is backed up once a day.
> I'm afraid that some of these properties are causing slowdowns and
> deadlocks.
>
> What would be a reasonable size to set the db and transaction log to at
> this
> growth rate? And grow it by what % or MB? I don't really know what to even
> start with and I don't want to do something that will make performance
> worse.
> Thanks for any help.
> --
> Judy

Database analysis

Hi,
Is there a tool to give me a report on all tables contained within a
microsoft DB. Looking to see total rows in each table, total size of
table.
Many Thanks
GarethOn Sep 18, 1:52 pm, payneless <gpayne_one...@.hotmail.com> wrote:
> Hi,
> Is there a tool to give me a report on all tables contained within a
> microsoft DB. Looking to see total rows in each table, total size of
> table.
> Many Thanks
> Gareth
you can try the below query
DECLARE @.Table varchar(255)
DECLARE @.NumberOfRows int
SET @.Table = 'titles'
SET @.NumberOfRows = 1
CREATE TABLE #t (name varchar(255),rows int, reserved
varchar(100),data varchar(100),index_size varchar(100), unused
varchar(100))
INSERT INTO #t
EXEC sp_MSforeachtable @.command1="sp_spaceused '?'"
SELECT * FROM #t
DROP TABLE #t
Thanks
Vijayasekar|||If you are in a SQL Server 2005 Environment and SSMS is installed then go to
a database and right click on it. Choose Reports from the popup menu and
select "Disk Usage by Table" or try other choices for alternative results.
--
Ekrem Önsoy
"payneless" <gpayne_onetel@.hotmail.com> wrote in message
news:1190105577.773630.209660@.50g2000hsm.googlegroups.com...
> Hi,
> Is there a tool to give me a report on all tables contained within a
> microsoft DB. Looking to see total rows in each table, total size of
> table.
> Many Thanks
> Gareth
>|||If you are in SQL Server install SQL Server 2005 Performance Dashboard
Reports
http://www.microsoft.com/downloads/details.aspx?FamilyID=1d3a4a0d-7e0c-4730-8204-e419218c1efc&DisplayLang=en
"payneless" <gpayne_onetel@.hotmail.com> wrote in message
news:1190105577.773630.209660@.50g2000hsm.googlegroups.com...
> Hi,
> Is there a tool to give me a report on all tables contained within a
> microsoft DB. Looking to see total rows in each table, total size of
> table.
> Many Thanks
> Gareth
>

Database & t-log file size

I have a database that is about 60GB only half of which is used space -
the transaction log is 31GB with only 100MB of used space. Is it safe
to shrink these as both are on separate drives and need the free space.
J1C wrote:
> I have a database that is about 60GB only half of which is used space -
> the transaction log is 31GB with only 100MB of used space. Is it safe
> to shrink these as both are on separate drives and need the free space.
>
What recovery model are you using for this database? If Full or
Bulk-Logged, are you running transaction log backups at regular
intervals? Log file growth occurs when:
- database is in Simple mode, the log file will grow large enough to
hold any transaction that is executed. Committed transactions are
automatically purged from the log, but the physical file size is not
decreased.
- database is in Full or Bulk-Logged mode, transactions are purged when
a transaction log backup occurs. Committed transactions that are not
flagged for replication are purged. The physical file size is not
decreased.
The log file is a "journal", recording all of the changes made in your
database. If a large transaction is submitted that affects 30GB of
data, the transaction log will grow to 30GB in order to hold that
transaction. Once the transaction is committed, it will be flushed out
of the log (automatically if in Simple mode, upon t-log backup
otherwise). The log file will remain 30GB in size however. Use DBCC
SHRINKFILE to reclaim that space. Consider the cause of the growth,
however, there's no point shrinking if it's just going to grow again.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||J1C wrote:
> I have a database that is about 60GB only half of which is used space -
> the transaction log is 31GB with only 100MB of used space. Is it safe
> to shrink these as both are on separate drives and need the free space.
Wrong question. The right question is how much space do you need to run
your database? We can't possibly tell you that. Get some estimates from
the DBA or from whoever developed or supports the system.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

Database & t-log file size

I have a database that is about 60GB only half of which is used space -
the transaction log is 31GB with only 100MB of used space. Is it safe
to shrink these as both are on separate drives and need the free space.J1C wrote:
> I have a database that is about 60GB only half of which is used space -
> the transaction log is 31GB with only 100MB of used space. Is it safe
> to shrink these as both are on separate drives and need the free space.
>
What recovery model are you using for this database? If Full or
Bulk-Logged, are you running transaction log backups at regular
intervals? Log file growth occurs when:
- database is in Simple mode, the log file will grow large enough to
hold any transaction that is executed. Committed transactions are
automatically purged from the log, but the physical file size is not
decreased.
- database is in Full or Bulk-Logged mode, transactions are purged when
a transaction log backup occurs. Committed transactions that are not
flagged for replication are purged. The physical file size is not
decreased.
The log file is a "journal", recording all of the changes made in your
database. If a large transaction is submitted that affects 30GB of
data, the transaction log will grow to 30GB in order to hold that
transaction. Once the transaction is committed, it will be flushed out
of the log (automatically if in Simple mode, upon t-log backup
otherwise). The log file will remain 30GB in size however. Use DBCC
SHRINKFILE to reclaim that space. Consider the cause of the growth,
however, there's no point shrinking if it's just going to grow again.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||J1C wrote:
> I have a database that is about 60GB only half of which is used space -
> the transaction log is 31GB with only 100MB of used space. Is it safe
> to shrink these as both are on separate drives and need the free space.
Wrong question. The right question is how much space do you need to run
your database? We can't possibly tell you that. Get some estimates from
the DBA or from whoever developed or supports the system.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Database & t-log file size

I have a database that is about 60GB only half of which is used space -
the transaction log is 31GB with only 100MB of used space. Is it safe
to shrink these as both are on separate drives and need the free space.My recommendatiion is to keep the MDF (data) file as it is because this will
ensure that you have enough space in data file and will help you not to
autogrow by itself. But you could shrink the LDF file using DBCC SHRINKFILE
.
If the database is in development you could change the recovery model to
SIMPLE and truncate the log using BACKUP LOG <DBNAME> with Truncate_only and
then shrink the file. If it is production then have the recovery model as
FULL and then schedule a transaction log backup in frequent intervals. This
will help to keep the LDF file not to grow as well as help you to do point i
n
time recovery if needed.
Thanks
Hari
"J1C" wrote:

> I have a database that is about 60GB only half of which is used space -
> the transaction log is 31GB with only 100MB of used space. Is it safe
> to shrink these as both are on separate drives and need the free space.
>|||J1C wrote:
> I have a database that is about 60GB only half of which is used space -
> the transaction log is 31GB with only 100MB of used space. Is it safe
> to shrink these as both are on separate drives and need the free space.
>
What recovery model are you using for this database? If Full or
Bulk-Logged, are you running transaction log backups at regular
intervals? Log file growth occurs when:
- database is in Simple mode, the log file will grow large enough to
hold any transaction that is executed. Committed transactions are
automatically purged from the log, but the physical file size is not
decreased.
- database is in Full or Bulk-Logged mode, transactions are purged when
a transaction log backup occurs. Committed transactions that are not
flagged for replication are purged. The physical file size is not
decreased.
The log file is a "journal", recording all of the changes made in your
database. If a large transaction is submitted that affects 30GB of
data, the transaction log will grow to 30GB in order to hold that
transaction. Once the transaction is committed, it will be flushed out
of the log (automatically if in Simple mode, upon t-log backup
otherwise). The log file will remain 30GB in size however. Use DBCC
SHRINKFILE to reclaim that space. Consider the cause of the growth,
however, there's no point shrinking if it's just going to grow again.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||J1C wrote:
> I have a database that is about 60GB only half of which is used space -
> the transaction log is 31GB with only 100MB of used space. Is it safe
> to shrink these as both are on separate drives and need the free space.
Wrong question. The right question is how much space do you need to run
your database? We can't possibly tell you that. Get some estimates from
the DBA or from whoever developed or supports the system.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Database "corruption" causing terrible performance?

We have a database that has (until recently) exhibited great performance
[SQL Server 2000, Data (MDF) size = 50GB].
The schema has not changed for months, the only thing that changes is the
data.
The other day, performance of the web-site dropped to almost zero...30
seconds for a page to appear that normally would appear in milliseconds.
I first checked the schema by comparing it against a similar database. The
schema (indexes etc) were identical.
I then closed the website to all users except me. I switched on SQL
profiler and opened the site. As soon as a few queries hit the machine, the
CPU flat-lined at 100% and stayed there for a minute or two.
I ran DBCC SHOWCONTIG and CHECKDB. None of these showed anything untoward.
However, I ran a tool to completely rebuild every index (took most of the
day). After that, performance had not improved. I increased the size of
the data and log files for both this and the tempdb - no effect. I updated
the statistics etc, but again still to no avail. I considered defragmenting
the disc, but the log and data file were hardly fragmented at all. What
else to try?
In desperation, I created a brand new database with identical schema and
using DTS, copied all the data into this database from the original database
(took overnight). I am pleased to say that the web site now has blistering
performance once again!
OK - so what's wrong with my original database that the DBCC diagnostic
tools fail to show? Investigating further this morning, I found the
following:
When I right click on [newDB].[tableA] and select properties, it informs me
that there are 2365000 rows. Select count(*) ... agrees with this value.
However, if I do the same with [originalDB].[tableA] the properties tell me
that there are 0 (zero) rows, but select count(*) shows there to be 2365000
rows.
I don't know enough of the internals of SQL Server, but I'm guessing that
the code that informs the Properties page that there are zero rows might be
the same code that helps the Optimiser decide whether to use an index or
not.
If this is the case, then I'm hoping that there is a tool that I've
overlooked that can correct these statistics. If so, can someone let me
know what this tool is? Also, why would SQL Server get into this state (and
has it been fixed in 2005/2008)?
Thanks to everyone who reads this
Griff
Griff
Did you identify those long ruinning queries by Profiler, did not you? See
an execution plan of these queries, do they perfom table/index scan?
"Griff" <GriffithsJ_520@.htomail.com> wrote in message
news:%23PTxDaNiIHA.4320@.TK2MSFTNGP06.phx.gbl...
> We have a database that has (until recently) exhibited great performance
> [SQL Server 2000, Data (MDF) size = 50GB].
> The schema has not changed for months, the only thing that changes is the
> data.
> The other day, performance of the web-site dropped to almost zero...30
> seconds for a page to appear that normally would appear in milliseconds.
> I first checked the schema by comparing it against a similar database.
> The schema (indexes etc) were identical.
> I then closed the website to all users except me. I switched on SQL
> profiler and opened the site. As soon as a few queries hit the machine,
> the CPU flat-lined at 100% and stayed there for a minute or two.
> I ran DBCC SHOWCONTIG and CHECKDB. None of these showed anything
> untoward. However, I ran a tool to completely rebuild every index (took
> most of the day). After that, performance had not improved. I increased
> the size of the data and log files for both this and the tempdb - no
> effect. I updated the statistics etc, but again still to no avail. I
> considered defragmenting the disc, but the log and data file were hardly
> fragmented at all. What else to try?
> In desperation, I created a brand new database with identical schema and
> using DTS, copied all the data into this database from the original
> database (took overnight). I am pleased to say that the web site now has
> blistering performance once again!
> OK - so what's wrong with my original database that the DBCC diagnostic
> tools fail to show? Investigating further this morning, I found the
> following:
> When I right click on [newDB].[tableA] and select properties, it informs
> me that there are 2365000 rows. Select count(*) ... agrees with this
> value. However, if I do the same with [originalDB].[tableA] the properties
> tell me that there are 0 (zero) rows, but select count(*) shows there to
> be 2365000 rows.
> I don't know enough of the internals of SQL Server, but I'm guessing that
> the code that informs the Properties page that there are zero rows might
> be the same code that helps the Optimiser decide whether to use an index
> or not.
> If this is the case, then I'm hoping that there is a tool that I've
> overlooked that can correct these statistics. If so, can someone let me
> know what this tool is? Also, why would SQL Server get into this state
> (and has it been fixed in 2005/2008)?
> Thanks to everyone who reads this
> Griff
>
|||> Did you identify those long ruinning queries by Profiler, did not you?
> See an execution plan of these queries, do they perfom table/index scan?
A very good question. Sadly, the answer is "no" and unfortunately, the site
is now live again and I'm not allowed to perform such tests on this database
as it will degrade the live DB's performance. I'll have to copy this rather
large DB to our DEV area to perform such a test.
As I mentioned, TableA shows "zero rows" when one pulls up the properties
page. Where is this information held?
I looked in the sysindexes table at the rows value. From my understanding,
if there is a clustered index, then the true row count should be returned
and then this value used for all non-clustered indexes. Now, this table
does not have a clustered index. There are two "indexes". The first one is
a non-clustered index and the rows value = 0 (which is what I'd expect).
The second is a primary key and the rows value is not 0 (which is what the
MSDN documentation would lead me to expect) but instead 258067. However,
the ACTUAL row count is 2365232 [select count()) from ...], so where the
rows value came from I've really no idea.
I've another example from TableB. This DOES have a clustered index. The
rows value here is 6180637 which agrees with Count(*). However, there are 5
other non-clustered indexes which all have the same rows value of 6177790 -
MSDN leads me to expect that this should equal the rows value for the
clustered index, but evidently it doesn't. And yes, I had completely rebuilt
the indexes and then run 'update statistics TableB' command before running
the query on sysindexes.
Griff
|||Interesting (well, I guess that depends...)
When I run the command:
DBCC SHOW_STATISTICS (TABLEA, TableA_IndexName)
On the new database, it returns three data sets (using query analyser here)
and the following message:
Statistics for INDEX 'TableA_IndexName'.
(1 row(s) affected)
(2 row(s) affected)
(200 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
However...
When I run this on the original database, it returns NO data sets, just the
following message:
Statistics for INDEX 'TableA_IndexName'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Griff
|||Griff
Create a clustered index on TableA, and again run SQL Server Profile to see
what is going on the server.
"Griff" <GriffithsJ_520@.htomail.com> wrote in message
news:OusS1iOiIHA.5724@.TK2MSFTNGP05.phx.gbl...
> A very good question. Sadly, the answer is "no" and unfortunately, the
> site is now live again and I'm not allowed to perform such tests on this
> database as it will degrade the live DB's performance. I'll have to copy
> this rather large DB to our DEV area to perform such a test.
> As I mentioned, TableA shows "zero rows" when one pulls up the properties
> page. Where is this information held?
> I looked in the sysindexes table at the rows value. From my
> understanding, if there is a clustered index, then the true row count
> should be returned and then this value used for all non-clustered indexes.
> Now, this table does not have a clustered index. There are two "indexes".
> The first one is a non-clustered index and the rows value = 0 (which is
> what I'd expect). The second is a primary key and the rows value is not 0
> (which is what the MSDN documentation would lead me to expect) but instead
> 258067. However, the ACTUAL row count is 2365232 [select count()) from
> ...], so where the rows value came from I've really no idea.
> I've another example from TableB. This DOES have a clustered index. The
> rows value here is 6180637 which agrees with Count(*). However, there are
> 5 other non-clustered indexes which all have the same rows value of
> 6177790 - MSDN leads me to expect that this should equal the rows value
> for the clustered index, but evidently it doesn't. And yes, I had
> completely rebuilt the indexes and then run 'update statistics TableB'
> command before running the query on sysindexes.
> Griff
>