How do I manage the size of Sql Server's error log? I am not referring to th
e
transaction logs for each database. It is the log that stores successful and
unsuccessful logins (among other things). In Enterprise Manager, it is under
"Management", "logs". This file keeps growing by around 10 Mb each day. When
I click on it from Enterprise Manager, it takes 20 minutes or logner to load
and is getting longer each day. I then export this list to find out who has
successfully logged in in the last week. I can't find any place to limit its
growth. Or, is there a better way of getting a table of successful logins?
Thanks in advance!Brad,
The file is recreated each time the SQL Server service is restarted. You
can control the size of the log by periodically running the
sp_cycle_errorlog (see BOL) system stored procedure.
HTH
J
"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
> How do I manage the size of Sql Server's error log? I am not referring to
> the
> transaction logs for each database. It is the log that stores successful
> and
> unsuccessful logins (among other things). In Enterprise Manager, it is
> under
> "Management", "logs". This file keeps growing by around 10 Mb each day.
> When
> I click on it from Enterprise Manager, it takes 20 minutes or logner to
> load
> and is getting longer each day. I then export this list to find out who
> has
> successfully logged in in the last week. I can't find any place to limit
> its
> growth. Or, is there a better way of getting a table of successful logins?
> Thanks in advance!|||"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
> How do I manage the size of Sql Server's error log? I am not referring to
> the
> transaction logs for each database. It is the log that stores successful
> and
> unsuccessful logins (among other things). In Enterprise Manager, it is
> under
> "Management", "logs". This file keeps growing by around 10 Mb each day.
> When
> I click on it from Enterprise Manager, it takes 20 minutes or logner to
> load
> and is getting longer each day. I then export this list to find out who
> has
> successfully logged in in the last week. I can't find any place to limit
> its
> growth. Or, is there a better way of getting a table of successful logins?
Do not keep it from growing, just look at it as a text file, at
\\server\drive$\mssql\log\errorlog. If log grows big, EM is not the tool to
look at it.
Or take a look at "Configure SQL Server Error Logs" in BOL.
Regards
Wojtek|||Hi,
Please do not open the error log from Enterprise manager -- management if
the file is huge. It takes lots of resources and it is not a good practice
if your
file is bigger.
Best method is connect to your server and use the WINDOWS explorer and go to
SQL Server program groups / Logs folder. Errorlog with out a number
will be the latest file.
Incase if you need to open the file using enterprise manager then schedule a
job with system procedure sp_cycle_errorlog
2 to 3 times a day to recyle the error log using sql agent jobs. This will
ensure that error log file size is limted. In this case you can open the
file using
enterprise manager.
Thanks
Hari
SQL Server MVP
"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
> How do I manage the size of Sql Server's error log? I am not referring to
> the
> transaction logs for each database. It is the log that stores successful
> and
> unsuccessful logins (among other things). In Enterprise Manager, it is
> under
> "Management", "logs". This file keeps growing by around 10 Mb each day.
> When
> I click on it from Enterprise Manager, it takes 20 minutes or logner to
> load
> and is getting longer each day. I then export this list to find out who
> has
> successfully logged in in the last week. I can't find any place to limit
> its
> growth. Or, is there a better way of getting a table of successful logins?
> Thanks in advance!|||All the other respondents are correct; however, you can modify the NUMBER of
Error Log files that are kept before they are recycled. In SQL EM,
right-click on the SQL Logs folder and choose properties. You will see that
the default is grayed-out but is for 6 files. You can check the box and
then choose the number you'd like to keep the history for.
We usually only audit Failures as Successes can chew up quite a bit of space
on a busy server. Also, not only are the successes logged in the SQL Server
Error log but also the server's Application Event Log.
Finally, I usually use the xp_readerrorlog extended stored procedure. No
parameter gives you the current log. An integer value gives you the file
with the same extention: 1 for Errorlog.1, 2 for Errorlog.2, etc.
Sincerely,
Anthony Thomas
"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
How do I manage the size of Sql Server's error log? I am not referring to
the
transaction logs for each database. It is the log that stores successful and
unsuccessful logins (among other things). In Enterprise Manager, it is under
"Management", "logs". This file keeps growing by around 10 Mb each day. When
I click on it from Enterprise Manager, it takes 20 minutes or logner to load
and is getting longer each day. I then export this list to find out who has
successfully logged in in the last week. I can't find any place to limit its
growth. Or, is there a better way of getting a table of successful logins?
Thanks in advance!
Showing posts with label size. Show all posts
Showing posts with label size. Show all posts
Friday, March 30, 2012
Manage size of error log
How do I manage the size of Sql Server's error log? I am not referring to the
transaction logs for each database. It is the log that stores successful and
unsuccessful logins (among other things). In Enterprise Manager, it is under
"Management", "logs". This file keeps growing by around 10 Mb each day. When
I click on it from Enterprise Manager, it takes 20 minutes or logner to load
and is getting longer each day. I then export this list to find out who has
successfully logged in in the last week. I can't find any place to limit its
growth. Or, is there a better way of getting a table of successful logins?
Thanks in advance!Brad,
The file is recreated each time the SQL Server service is restarted. You
can control the size of the log by periodically running the
sp_cycle_errorlog (see BOL) system stored procedure.
HTH
J
"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
> How do I manage the size of Sql Server's error log? I am not referring to
> the
> transaction logs for each database. It is the log that stores successful
> and
> unsuccessful logins (among other things). In Enterprise Manager, it is
> under
> "Management", "logs". This file keeps growing by around 10 Mb each day.
> When
> I click on it from Enterprise Manager, it takes 20 minutes or logner to
> load
> and is getting longer each day. I then export this list to find out who
> has
> successfully logged in in the last week. I can't find any place to limit
> its
> growth. Or, is there a better way of getting a table of successful logins?
> Thanks in advance!|||"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
> How do I manage the size of Sql Server's error log? I am not referring to
> the
> transaction logs for each database. It is the log that stores successful
> and
> unsuccessful logins (among other things). In Enterprise Manager, it is
> under
> "Management", "logs". This file keeps growing by around 10 Mb each day.
> When
> I click on it from Enterprise Manager, it takes 20 minutes or logner to
> load
> and is getting longer each day. I then export this list to find out who
> has
> successfully logged in in the last week. I can't find any place to limit
> its
> growth. Or, is there a better way of getting a table of successful logins?
Do not keep it from growing, just look at it as a text file, at
\\server\drive$\mssql\log\errorlog. If log grows big, EM is not the tool to
look at it.
Or take a look at "Configure SQL Server Error Logs" in BOL.
Regards
Wojtek|||Hi,
Please do not open the error log from Enterprise manager -- management if
the file is huge. It takes lots of resources and it is not a good practice
if your
file is bigger.
Best method is connect to your server and use the WINDOWS explorer and go to
SQL Server program groups / Logs folder. Errorlog with out a number
will be the latest file.
Incase if you need to open the file using enterprise manager then schedule a
job with system procedure sp_cycle_errorlog
2 to 3 times a day to recyle the error log using sql agent jobs. This will
ensure that error log file size is limted. In this case you can open the
file using
enterprise manager.
Thanks
Hari
SQL Server MVP
"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
> How do I manage the size of Sql Server's error log? I am not referring to
> the
> transaction logs for each database. It is the log that stores successful
> and
> unsuccessful logins (among other things). In Enterprise Manager, it is
> under
> "Management", "logs". This file keeps growing by around 10 Mb each day.
> When
> I click on it from Enterprise Manager, it takes 20 minutes or logner to
> load
> and is getting longer each day. I then export this list to find out who
> has
> successfully logged in in the last week. I can't find any place to limit
> its
> growth. Or, is there a better way of getting a table of successful logins?
> Thanks in advance!|||All the other respondents are correct; however, you can modify the NUMBER of
Error Log files that are kept before they are recycled. In SQL EM,
right-click on the SQL Logs folder and choose properties. You will see that
the default is grayed-out but is for 6 files. You can check the box and
then choose the number you'd like to keep the history for.
We usually only audit Failures as Successes can chew up quite a bit of space
on a busy server. Also, not only are the successes logged in the SQL Server
Error log but also the server's Application Event Log.
Finally, I usually use the xp_readerrorlog extended stored procedure. No
parameter gives you the current log. An integer value gives you the file
with the same extention: 1 for Errorlog.1, 2 for Errorlog.2, etc.
Sincerely,
Anthony Thomas
"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
How do I manage the size of Sql Server's error log? I am not referring to
the
transaction logs for each database. It is the log that stores successful and
unsuccessful logins (among other things). In Enterprise Manager, it is under
"Management", "logs". This file keeps growing by around 10 Mb each day. When
I click on it from Enterprise Manager, it takes 20 minutes or logner to load
and is getting longer each day. I then export this list to find out who has
successfully logged in in the last week. I can't find any place to limit its
growth. Or, is there a better way of getting a table of successful logins?
Thanks in advance!sql
transaction logs for each database. It is the log that stores successful and
unsuccessful logins (among other things). In Enterprise Manager, it is under
"Management", "logs". This file keeps growing by around 10 Mb each day. When
I click on it from Enterprise Manager, it takes 20 minutes or logner to load
and is getting longer each day. I then export this list to find out who has
successfully logged in in the last week. I can't find any place to limit its
growth. Or, is there a better way of getting a table of successful logins?
Thanks in advance!Brad,
The file is recreated each time the SQL Server service is restarted. You
can control the size of the log by periodically running the
sp_cycle_errorlog (see BOL) system stored procedure.
HTH
J
"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
> How do I manage the size of Sql Server's error log? I am not referring to
> the
> transaction logs for each database. It is the log that stores successful
> and
> unsuccessful logins (among other things). In Enterprise Manager, it is
> under
> "Management", "logs". This file keeps growing by around 10 Mb each day.
> When
> I click on it from Enterprise Manager, it takes 20 minutes or logner to
> load
> and is getting longer each day. I then export this list to find out who
> has
> successfully logged in in the last week. I can't find any place to limit
> its
> growth. Or, is there a better way of getting a table of successful logins?
> Thanks in advance!|||"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
> How do I manage the size of Sql Server's error log? I am not referring to
> the
> transaction logs for each database. It is the log that stores successful
> and
> unsuccessful logins (among other things). In Enterprise Manager, it is
> under
> "Management", "logs". This file keeps growing by around 10 Mb each day.
> When
> I click on it from Enterprise Manager, it takes 20 minutes or logner to
> load
> and is getting longer each day. I then export this list to find out who
> has
> successfully logged in in the last week. I can't find any place to limit
> its
> growth. Or, is there a better way of getting a table of successful logins?
Do not keep it from growing, just look at it as a text file, at
\\server\drive$\mssql\log\errorlog. If log grows big, EM is not the tool to
look at it.
Or take a look at "Configure SQL Server Error Logs" in BOL.
Regards
Wojtek|||Hi,
Please do not open the error log from Enterprise manager -- management if
the file is huge. It takes lots of resources and it is not a good practice
if your
file is bigger.
Best method is connect to your server and use the WINDOWS explorer and go to
SQL Server program groups / Logs folder. Errorlog with out a number
will be the latest file.
Incase if you need to open the file using enterprise manager then schedule a
job with system procedure sp_cycle_errorlog
2 to 3 times a day to recyle the error log using sql agent jobs. This will
ensure that error log file size is limted. In this case you can open the
file using
enterprise manager.
Thanks
Hari
SQL Server MVP
"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
> How do I manage the size of Sql Server's error log? I am not referring to
> the
> transaction logs for each database. It is the log that stores successful
> and
> unsuccessful logins (among other things). In Enterprise Manager, it is
> under
> "Management", "logs". This file keeps growing by around 10 Mb each day.
> When
> I click on it from Enterprise Manager, it takes 20 minutes or logner to
> load
> and is getting longer each day. I then export this list to find out who
> has
> successfully logged in in the last week. I can't find any place to limit
> its
> growth. Or, is there a better way of getting a table of successful logins?
> Thanks in advance!|||All the other respondents are correct; however, you can modify the NUMBER of
Error Log files that are kept before they are recycled. In SQL EM,
right-click on the SQL Logs folder and choose properties. You will see that
the default is grayed-out but is for 6 files. You can check the box and
then choose the number you'd like to keep the history for.
We usually only audit Failures as Successes can chew up quite a bit of space
on a busy server. Also, not only are the successes logged in the SQL Server
Error log but also the server's Application Event Log.
Finally, I usually use the xp_readerrorlog extended stored procedure. No
parameter gives you the current log. An integer value gives you the file
with the same extention: 1 for Errorlog.1, 2 for Errorlog.2, etc.
Sincerely,
Anthony Thomas
"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
How do I manage the size of Sql Server's error log? I am not referring to
the
transaction logs for each database. It is the log that stores successful and
unsuccessful logins (among other things). In Enterprise Manager, it is under
"Management", "logs". This file keeps growing by around 10 Mb each day. When
I click on it from Enterprise Manager, it takes 20 minutes or logner to load
and is getting longer each day. I then export this list to find out who has
successfully logged in in the last week. I can't find any place to limit its
growth. Or, is there a better way of getting a table of successful logins?
Thanks in advance!sql
Manage size of error log
How do I manage the size of Sql Server's error log? I am not referring to the
transaction logs for each database. It is the log that stores successful and
unsuccessful logins (among other things). In Enterprise Manager, it is under
"Management", "logs". This file keeps growing by around 10 Mb each day. When
I click on it from Enterprise Manager, it takes 20 minutes or logner to load
and is getting longer each day. I then export this list to find out who has
successfully logged in in the last week. I can't find any place to limit its
growth. Or, is there a better way of getting a table of successful logins?
Thanks in advance!
Brad,
The file is recreated each time the SQL Server service is restarted. You
can control the size of the log by periodically running the
sp_cycle_errorlog (see BOL) system stored procedure.
HTH
J
"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
> How do I manage the size of Sql Server's error log? I am not referring to
> the
> transaction logs for each database. It is the log that stores successful
> and
> unsuccessful logins (among other things). In Enterprise Manager, it is
> under
> "Management", "logs". This file keeps growing by around 10 Mb each day.
> When
> I click on it from Enterprise Manager, it takes 20 minutes or logner to
> load
> and is getting longer each day. I then export this list to find out who
> has
> successfully logged in in the last week. I can't find any place to limit
> its
> growth. Or, is there a better way of getting a table of successful logins?
> Thanks in advance!
|||"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
> How do I manage the size of Sql Server's error log? I am not referring to
> the
> transaction logs for each database. It is the log that stores successful
> and
> unsuccessful logins (among other things). In Enterprise Manager, it is
> under
> "Management", "logs". This file keeps growing by around 10 Mb each day.
> When
> I click on it from Enterprise Manager, it takes 20 minutes or logner to
> load
> and is getting longer each day. I then export this list to find out who
> has
> successfully logged in in the last week. I can't find any place to limit
> its
> growth. Or, is there a better way of getting a table of successful logins?
Do not keep it from growing, just look at it as a text file, at
\\server\drive$\mssql\log\errorlog. If log grows big, EM is not the tool to
look at it.
Or take a look at "Configure SQL Server Error Logs" in BOL.
Regards
Wojtek
|||Hi,
Please do not open the error log from Enterprise manager -- management if
the file is huge. It takes lots of resources and it is not a good practice
if your
file is bigger.
Best method is connect to your server and use the WINDOWS explorer and go to
SQL Server program groups / Logs folder. Errorlog with out a number
will be the latest file.
Incase if you need to open the file using enterprise manager then schedule a
job with system procedure sp_cycle_errorlog
2 to 3 times a day to recyle the error log using sql agent jobs. This will
ensure that error log file size is limted. In this case you can open the
file using
enterprise manager.
Thanks
Hari
SQL Server MVP
"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
> How do I manage the size of Sql Server's error log? I am not referring to
> the
> transaction logs for each database. It is the log that stores successful
> and
> unsuccessful logins (among other things). In Enterprise Manager, it is
> under
> "Management", "logs". This file keeps growing by around 10 Mb each day.
> When
> I click on it from Enterprise Manager, it takes 20 minutes or logner to
> load
> and is getting longer each day. I then export this list to find out who
> has
> successfully logged in in the last week. I can't find any place to limit
> its
> growth. Or, is there a better way of getting a table of successful logins?
> Thanks in advance!
|||All the other respondents are correct; however, you can modify the NUMBER of
Error Log files that are kept before they are recycled. In SQL EM,
right-click on the SQL Logs folder and choose properties. You will see that
the default is grayed-out but is for 6 files. You can check the box and
then choose the number you'd like to keep the history for.
We usually only audit Failures as Successes can chew up quite a bit of space
on a busy server. Also, not only are the successes logged in the SQL Server
Error log but also the server's Application Event Log.
Finally, I usually use the xp_readerrorlog extended stored procedure. No
parameter gives you the current log. An integer value gives you the file
with the same extention: 1 for Errorlog.1, 2 for Errorlog.2, etc.
Sincerely,
Anthony Thomas
"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
How do I manage the size of Sql Server's error log? I am not referring to
the
transaction logs for each database. It is the log that stores successful and
unsuccessful logins (among other things). In Enterprise Manager, it is under
"Management", "logs". This file keeps growing by around 10 Mb each day. When
I click on it from Enterprise Manager, it takes 20 minutes or logner to load
and is getting longer each day. I then export this list to find out who has
successfully logged in in the last week. I can't find any place to limit its
growth. Or, is there a better way of getting a table of successful logins?
Thanks in advance!
transaction logs for each database. It is the log that stores successful and
unsuccessful logins (among other things). In Enterprise Manager, it is under
"Management", "logs". This file keeps growing by around 10 Mb each day. When
I click on it from Enterprise Manager, it takes 20 minutes or logner to load
and is getting longer each day. I then export this list to find out who has
successfully logged in in the last week. I can't find any place to limit its
growth. Or, is there a better way of getting a table of successful logins?
Thanks in advance!
Brad,
The file is recreated each time the SQL Server service is restarted. You
can control the size of the log by periodically running the
sp_cycle_errorlog (see BOL) system stored procedure.
HTH
J
"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
> How do I manage the size of Sql Server's error log? I am not referring to
> the
> transaction logs for each database. It is the log that stores successful
> and
> unsuccessful logins (among other things). In Enterprise Manager, it is
> under
> "Management", "logs". This file keeps growing by around 10 Mb each day.
> When
> I click on it from Enterprise Manager, it takes 20 minutes or logner to
> load
> and is getting longer each day. I then export this list to find out who
> has
> successfully logged in in the last week. I can't find any place to limit
> its
> growth. Or, is there a better way of getting a table of successful logins?
> Thanks in advance!
|||"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
> How do I manage the size of Sql Server's error log? I am not referring to
> the
> transaction logs for each database. It is the log that stores successful
> and
> unsuccessful logins (among other things). In Enterprise Manager, it is
> under
> "Management", "logs". This file keeps growing by around 10 Mb each day.
> When
> I click on it from Enterprise Manager, it takes 20 minutes or logner to
> load
> and is getting longer each day. I then export this list to find out who
> has
> successfully logged in in the last week. I can't find any place to limit
> its
> growth. Or, is there a better way of getting a table of successful logins?
Do not keep it from growing, just look at it as a text file, at
\\server\drive$\mssql\log\errorlog. If log grows big, EM is not the tool to
look at it.
Or take a look at "Configure SQL Server Error Logs" in BOL.
Regards
Wojtek
|||Hi,
Please do not open the error log from Enterprise manager -- management if
the file is huge. It takes lots of resources and it is not a good practice
if your
file is bigger.
Best method is connect to your server and use the WINDOWS explorer and go to
SQL Server program groups / Logs folder. Errorlog with out a number
will be the latest file.
Incase if you need to open the file using enterprise manager then schedule a
job with system procedure sp_cycle_errorlog
2 to 3 times a day to recyle the error log using sql agent jobs. This will
ensure that error log file size is limted. In this case you can open the
file using
enterprise manager.
Thanks
Hari
SQL Server MVP
"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
> How do I manage the size of Sql Server's error log? I am not referring to
> the
> transaction logs for each database. It is the log that stores successful
> and
> unsuccessful logins (among other things). In Enterprise Manager, it is
> under
> "Management", "logs". This file keeps growing by around 10 Mb each day.
> When
> I click on it from Enterprise Manager, it takes 20 minutes or logner to
> load
> and is getting longer each day. I then export this list to find out who
> has
> successfully logged in in the last week. I can't find any place to limit
> its
> growth. Or, is there a better way of getting a table of successful logins?
> Thanks in advance!
|||All the other respondents are correct; however, you can modify the NUMBER of
Error Log files that are kept before they are recycled. In SQL EM,
right-click on the SQL Logs folder and choose properties. You will see that
the default is grayed-out but is for 6 files. You can check the box and
then choose the number you'd like to keep the history for.
We usually only audit Failures as Successes can chew up quite a bit of space
on a busy server. Also, not only are the successes logged in the SQL Server
Error log but also the server's Application Event Log.
Finally, I usually use the xp_readerrorlog extended stored procedure. No
parameter gives you the current log. An integer value gives you the file
with the same extention: 1 for Errorlog.1, 2 for Errorlog.2, etc.
Sincerely,
Anthony Thomas
"Brad Burke Fusion Data" <BradBurkeFusionData@.discussions.microsoft.com>
wrote in message news:9AAC3DC2-7059-4E6F-BCAA-EC25C1203D3F@.microsoft.com...
How do I manage the size of Sql Server's error log? I am not referring to
the
transaction logs for each database. It is the log that stores successful and
unsuccessful logins (among other things). In Enterprise Manager, it is under
"Management", "logs". This file keeps growing by around 10 Mb each day. When
I click on it from Enterprise Manager, it takes 20 minutes or logner to load
and is getting longer each day. I then export this list to find out who has
successfully logged in in the last week. I can't find any place to limit its
growth. Or, is there a better way of getting a table of successful logins?
Thanks in advance!
Wednesday, March 28, 2012
Manage huge amount of data
Requirement:
1 billion records to be inserted every day
180 days data to be maintained
180 * 1 billion records = approx no of records = defines the size of the
database
Need to design the database/process to maintain so huge data?
- RHi Rakesh,
Need more info like an idea of the number of tables, size of rows, querying
which will reflect what indexes are required etc...
You will need some good kit though, lots of disks; what sort of fault
tolerance are you looking at because backups are going to be a problem.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:CE1870BB-68DB-47B8-A1B8-EFE8C30FB333@.microsoft.com...
> Requirement:
> 1 billion records to be inserted every day
> 180 days data to be maintained
> 180 * 1 billion records = approx no of records = defines the size of the
> database
> Need to design the database/process to maintain so huge data?
>
> - R|||> 1 billion records to be inserted every day
> 180 days data to be maintained
> 180 * 1 billion records = approx no of records = defines the size of the
> database
With an average row size of 100 bytes, this calculates to about 18 TB of
usable space not including index overhead. You'll also need to sustain a
rate of over 10 thousand inserts per second 24x7.
Very large tables are often partitioned for manageability reasons and to
address backup issues like Tony mentioned. Attention to detail is very
important. Unless you have experience working with very large databases, I
suggest you engage consultants with VLDB experience to help you out. We can
help you with specific questions but a project of such magnitude requires
dedicated resources with specialized experience.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:CE1870BB-68DB-47B8-A1B8-EFE8C30FB333@.microsoft.com...
> Requirement:
> 1 billion records to be inserted every day
> 180 days data to be maintained
> 180 * 1 billion records = approx no of records = defines the size of the
> database
> Need to design the database/process to maintain so huge data?
>
> - R
1 billion records to be inserted every day
180 days data to be maintained
180 * 1 billion records = approx no of records = defines the size of the
database
Need to design the database/process to maintain so huge data?
- RHi Rakesh,
Need more info like an idea of the number of tables, size of rows, querying
which will reflect what indexes are required etc...
You will need some good kit though, lots of disks; what sort of fault
tolerance are you looking at because backups are going to be a problem.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:CE1870BB-68DB-47B8-A1B8-EFE8C30FB333@.microsoft.com...
> Requirement:
> 1 billion records to be inserted every day
> 180 days data to be maintained
> 180 * 1 billion records = approx no of records = defines the size of the
> database
> Need to design the database/process to maintain so huge data?
>
> - R|||> 1 billion records to be inserted every day
> 180 days data to be maintained
> 180 * 1 billion records = approx no of records = defines the size of the
> database
With an average row size of 100 bytes, this calculates to about 18 TB of
usable space not including index overhead. You'll also need to sustain a
rate of over 10 thousand inserts per second 24x7.
Very large tables are often partitioned for manageability reasons and to
address backup issues like Tony mentioned. Attention to detail is very
important. Unless you have experience working with very large databases, I
suggest you engage consultants with VLDB experience to help you out. We can
help you with specific questions but a project of such magnitude requires
dedicated resources with specialized experience.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:CE1870BB-68DB-47B8-A1B8-EFE8C30FB333@.microsoft.com...
> Requirement:
> 1 billion records to be inserted every day
> 180 days data to be maintained
> 180 * 1 billion records = approx no of records = defines the size of the
> database
> Need to design the database/process to maintain so huge data?
>
> - R
Monday, March 19, 2012
Make legend fitting
I've been trying to get a legend (alongside a pie chart) completely visible,
but can't seem to get it to work. Does the size of the legend come hand in
hand with the size of the plotting area? How can I individually set the size
of the legend?
Thanks in advance!Legend, chart, point labels, ... everything is rendered inside the chart
control on your report. So the size of the plot area does influence the size
of the legend. The chart is automatically rendered smaller if the legend is
larger and vice versa.
Getting the layout of the rendered report right is a bit of a hassle, I know.
I don't know of a way to specify the size of the legend or the chart
separately. All I found was that you can reposition the legend, and specify
its layout: row, column, or table (on the Legend tab of the Chart Properties
dialog box). That usually works for me.
My 2c.
Flip
"Peter von Aesch" wrote:
> I've been trying to get a legend (alongside a pie chart) completely visible,
> but can't seem to get it to work. Does the size of the legend come hand in
> hand with the size of the plotting area? How can I individually set the size
> of the legend?
> Thanks in advance!
but can't seem to get it to work. Does the size of the legend come hand in
hand with the size of the plotting area? How can I individually set the size
of the legend?
Thanks in advance!Legend, chart, point labels, ... everything is rendered inside the chart
control on your report. So the size of the plot area does influence the size
of the legend. The chart is automatically rendered smaller if the legend is
larger and vice versa.
Getting the layout of the rendered report right is a bit of a hassle, I know.
I don't know of a way to specify the size of the legend or the chart
separately. All I found was that you can reposition the legend, and specify
its layout: row, column, or table (on the Legend tab of the Chart Properties
dialog box). That usually works for me.
My 2c.
Flip
"Peter von Aesch" wrote:
> I've been trying to get a legend (alongside a pie chart) completely visible,
> but can't seem to get it to work. Does the size of the legend come hand in
> hand with the size of the plotting area? How can I individually set the size
> of the legend?
> Thanks in advance!
Make changes to a production database!
Hello faculties,
We've a production database that is being used by one of our clients
as a backend for his website. The database size is around 1GB.
We've a production database that is being used by one of our clients
as a backend for his website. The database size is around 1GB.
Recently we added some tables to our local database which is a
replica of the production database. Now we need to apply the same
changes at the production one aslo.
I've no clue about what steps should i implement.
Please guide me!
Thanks in advance
Debian
*** Sent via Developersdex http://www.developersdex.com ***The ideal solution is that you take the scripts which you used to make
the changes in test from your source control system and simply run them
against the production database. For a large number of .sql files (or a
large number of target servers/databases), this can be automated using
osql.exe in batch files, or perhaps a script in your preferred
scripting language.
However, if you made the changes in some non-repeatable way (such as
using Enterprise Manager), then you would need another solution. One
possibility is to use a database compare tool of some sort to generate
a script for you, but you might still need to make other site-specific
changes such as updating data or creating new logins. So whatever you
do for now, it would be a good idea to plan on working with scripts in
a source control system in the future.
Simon|||Build a TSQL script to perform the upgrade and then test it out.
If you didn't save your change scripts when you did your own upgrade
then you'll have to re-create them (and remember to save the scripts
next time!). Enterprise Manager can help with this (right-click the
database, select All Tasks > Generate Script).
To script the data you can use this proc:
http://vyaskn.tripod.com/code.htm#inserts
or, if the data is particularly large you may find it easier to dump it
to a file and use BCP to perform the load.
It may help to drop or disable some constraints and indexes for the
duration of the upgrade - that's assuming the relevant tables aren't in
use at the time.
Also, you may want to check out some of the diff and synchronization
utlilities, such as:
http://www.red-gate.com/sql/summary.htm
Hope this helps.
--
David Portas
SQL Server MVP
--|||debian mojo (debian_mojo@.yahoo.com) writes:
> Hello faculties,
> We've a production database that is being used by one of our clients
> as a backend for his website. The database size is around 1GB.
> Recently we added some tables to our local database which is a
> replica of the production database. Now we need to apply the same
> changes at the production one aslo.
> I've no clue about what steps should i implement.
In addition to what Simon and David said, I just like to underscore
that whatever you do, first run your upgrade scripts in a test environment.
Don't deploy until production, until everything is working OK in test.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Monday, March 12, 2012
Make a database in stand by
Hi All,
I have a server with a database size is about 180Gb, I am going to make this
database as a standby database as a part of Log shipping.
My question is : how to make this database as standby as it without
restoring from backup which mean leaving its files and changing the status
to able to process log shipping to it.
Thanks,You can't do that, I'm afraid. Log shipping is depending of the appropriate log records being
available and has been applied (REDO and UNDO) and this is controlled by the restore process.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mahmoud Amin" <mm.amin@.gmail.com> wrote in message news:uFi5$9F9HHA.1900@.TK2MSFTNGP02.phx.gbl...
> Hi All,
> I have a server with a database size is about 180Gb, I am going to make this database as a standby
> database as a part of Log shipping.
> My question is : how to make this database as standby as it without restoring from backup which
> mean leaving its files and changing the status to able to process log shipping to it.
> Thanks,
>|||so how to restore from existing files , then able to use it as a standby ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u7el6JG9HHA.4736@.TK2MSFTNGP06.phx.gbl...
> You can't do that, I'm afraid. Log shipping is depending of the
> appropriate log records being available and has been applied (REDO and
> UNDO) and this is controlled by the restore process.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Mahmoud Amin" <mm.amin@.gmail.com> wrote in message
> news:uFi5$9F9HHA.1900@.TK2MSFTNGP02.phx.gbl...
>> Hi All,
>> I have a server with a database size is about 180Gb, I am going to make
>> this database as a standby database as a part of Log shipping.
>> My question is : how to make this database as standby as it without
>> restoring from backup which mean leaving its files and changing the
>> status to able to process log shipping to it.
>> Thanks,
>|||What do you mean by "existing files"? If you refer to the mdf and ldf files, then no. If you refer
to a database backup file, then yes. Do the restore using NORECOVERY. And you now need an unbroken
chain of log backups to restore, since that database backup was created.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mahmoud Amin" <mm.amin@.gmail.com> wrote in message news:eooVKPG9HHA.4880@.TK2MSFTNGP03.phx.gbl...
> so how to restore from existing files , then able to use it as a standby ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:u7el6JG9HHA.4736@.TK2MSFTNGP06.phx.gbl...
>> You can't do that, I'm afraid. Log shipping is depending of the appropriate log records being
>> available and has been applied (REDO and UNDO) and this is controlled by the restore process.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Mahmoud Amin" <mm.amin@.gmail.com> wrote in message news:uFi5$9F9HHA.1900@.TK2MSFTNGP02.phx.gbl...
>> Hi All,
>> I have a server with a database size is about 180Gb, I am going to make this database as a
>> standby database as a part of Log shipping.
>> My question is : how to make this database as standby as it without restoring from backup which
>> mean leaving its files and changing the status to able to process log shipping to it.
>> Thanks,
>>
>
I have a server with a database size is about 180Gb, I am going to make this
database as a standby database as a part of Log shipping.
My question is : how to make this database as standby as it without
restoring from backup which mean leaving its files and changing the status
to able to process log shipping to it.
Thanks,You can't do that, I'm afraid. Log shipping is depending of the appropriate log records being
available and has been applied (REDO and UNDO) and this is controlled by the restore process.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mahmoud Amin" <mm.amin@.gmail.com> wrote in message news:uFi5$9F9HHA.1900@.TK2MSFTNGP02.phx.gbl...
> Hi All,
> I have a server with a database size is about 180Gb, I am going to make this database as a standby
> database as a part of Log shipping.
> My question is : how to make this database as standby as it without restoring from backup which
> mean leaving its files and changing the status to able to process log shipping to it.
> Thanks,
>|||so how to restore from existing files , then able to use it as a standby ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u7el6JG9HHA.4736@.TK2MSFTNGP06.phx.gbl...
> You can't do that, I'm afraid. Log shipping is depending of the
> appropriate log records being available and has been applied (REDO and
> UNDO) and this is controlled by the restore process.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Mahmoud Amin" <mm.amin@.gmail.com> wrote in message
> news:uFi5$9F9HHA.1900@.TK2MSFTNGP02.phx.gbl...
>> Hi All,
>> I have a server with a database size is about 180Gb, I am going to make
>> this database as a standby database as a part of Log shipping.
>> My question is : how to make this database as standby as it without
>> restoring from backup which mean leaving its files and changing the
>> status to able to process log shipping to it.
>> Thanks,
>|||What do you mean by "existing files"? If you refer to the mdf and ldf files, then no. If you refer
to a database backup file, then yes. Do the restore using NORECOVERY. And you now need an unbroken
chain of log backups to restore, since that database backup was created.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mahmoud Amin" <mm.amin@.gmail.com> wrote in message news:eooVKPG9HHA.4880@.TK2MSFTNGP03.phx.gbl...
> so how to restore from existing files , then able to use it as a standby ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:u7el6JG9HHA.4736@.TK2MSFTNGP06.phx.gbl...
>> You can't do that, I'm afraid. Log shipping is depending of the appropriate log records being
>> available and has been applied (REDO and UNDO) and this is controlled by the restore process.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Mahmoud Amin" <mm.amin@.gmail.com> wrote in message news:uFi5$9F9HHA.1900@.TK2MSFTNGP02.phx.gbl...
>> Hi All,
>> I have a server with a database size is about 180Gb, I am going to make this database as a
>> standby database as a part of Log shipping.
>> My question is : how to make this database as standby as it without restoring from backup which
>> mean leaving its files and changing the status to able to process log shipping to it.
>> Thanks,
>>
>
Friday, March 9, 2012
Maintinence Jobs vs Defag Jobs
Quick question, I have a maintinence job that optimizes (reorganizes back to
original size my databases back to the original amount of free space). I also
have jobs that defrag specific tables.
Will the maintinence job defrag as well as reorgainize.
I would like to get rid of my defrag jobs and have one maintinence job that
does both.
Yes. If you select Reorganize data and index pages in the maintenance
plans, it will execute dbcc dbreindex. If you select Remove unused
space from database files, it will execute dbcc shrinkdatabase.
Considerations in going this route is that dbreindex is your only
option in using a maintenance plan. There are different considerations
to take into account when determining whether to use dbreindex or
indexdefrag. You would want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
A consideration with shrinking is that it is a resource intensive
operation. Additionally, the maintenance plan will limit you to using
shrinkdatabase instead of shrinkfile. You have less control and
somewhat unpredictable individual file sizes when using
shrinkdatabase. If you shrink a database, then it grows back to the
size it was before the shrink and then you shrink it again, the
process can unnecessarily waste a lot of resources as well as
contributing to OS level fragmentation. You can find additional
information in this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
<robertatcbb@.discussions.microsoft.com> wrote:
>Quick question, I have a maintinence job that optimizes (reorganizes back to
>original size my databases back to the original amount of free space). I also
>have jobs that defrag specific tables.
>Will the maintinence job defrag as well as reorgainize.
>I would like to get rid of my defrag jobs and have one maintinence job that
>does both.
|||Another consideration is that shrinking introduces logical fragmentation
that dbcc dbreindex removes - if you reindex then shrink you're shooting
yourself in the foot...
You should ask yourself why you have to shrink repeatedly - the reason the
db size grows is that SQL needs the extra space. If the db size grows and
then remains relatively static, you'd do best to leave it alone otherwise
you're wasting resources as Sue says.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:knfvn0tq38v3a905ff208i5u134m35top5@.4ax.com...
> Yes. If you select Reorganize data and index pages in the maintenance
> plans, it will execute dbcc dbreindex. If you select Remove unused
> space from database files, it will execute dbcc shrinkdatabase.
> Considerations in going this route is that dbreindex is your only
> option in using a maintenance plan. There are different considerations
> to take into account when determining whether to use dbreindex or
> indexdefrag. You would want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx[vbcol=seagreen]
> A consideration with shrinking is that it is a resource intensive
> operation. Additionally, the maintenance plan will limit you to using
> shrinkdatabase instead of shrinkfile. You have less control and
> somewhat unpredictable individual file sizes when using
> shrinkdatabase. If you shrink a database, then it grows back to the
> size it was before the shrink and then you shrink it again, the
> process can unnecessarily waste a lot of resources as well as
> contributing to OS level fragmentation. You can find additional
> information in this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> -Sue
> On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
> <robertatcbb@.discussions.microsoft.com> wrote:
to[vbcol=seagreen]
also[vbcol=seagreen]
that
>
original size my databases back to the original amount of free space). I also
have jobs that defrag specific tables.
Will the maintinence job defrag as well as reorgainize.
I would like to get rid of my defrag jobs and have one maintinence job that
does both.
Yes. If you select Reorganize data and index pages in the maintenance
plans, it will execute dbcc dbreindex. If you select Remove unused
space from database files, it will execute dbcc shrinkdatabase.
Considerations in going this route is that dbreindex is your only
option in using a maintenance plan. There are different considerations
to take into account when determining whether to use dbreindex or
indexdefrag. You would want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
A consideration with shrinking is that it is a resource intensive
operation. Additionally, the maintenance plan will limit you to using
shrinkdatabase instead of shrinkfile. You have less control and
somewhat unpredictable individual file sizes when using
shrinkdatabase. If you shrink a database, then it grows back to the
size it was before the shrink and then you shrink it again, the
process can unnecessarily waste a lot of resources as well as
contributing to OS level fragmentation. You can find additional
information in this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
<robertatcbb@.discussions.microsoft.com> wrote:
>Quick question, I have a maintinence job that optimizes (reorganizes back to
>original size my databases back to the original amount of free space). I also
>have jobs that defrag specific tables.
>Will the maintinence job defrag as well as reorgainize.
>I would like to get rid of my defrag jobs and have one maintinence job that
>does both.
|||Another consideration is that shrinking introduces logical fragmentation
that dbcc dbreindex removes - if you reindex then shrink you're shooting
yourself in the foot...
You should ask yourself why you have to shrink repeatedly - the reason the
db size grows is that SQL needs the extra space. If the db size grows and
then remains relatively static, you'd do best to leave it alone otherwise
you're wasting resources as Sue says.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:knfvn0tq38v3a905ff208i5u134m35top5@.4ax.com...
> Yes. If you select Reorganize data and index pages in the maintenance
> plans, it will execute dbcc dbreindex. If you select Remove unused
> space from database files, it will execute dbcc shrinkdatabase.
> Considerations in going this route is that dbreindex is your only
> option in using a maintenance plan. There are different considerations
> to take into account when determining whether to use dbreindex or
> indexdefrag. You would want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx[vbcol=seagreen]
> A consideration with shrinking is that it is a resource intensive
> operation. Additionally, the maintenance plan will limit you to using
> shrinkdatabase instead of shrinkfile. You have less control and
> somewhat unpredictable individual file sizes when using
> shrinkdatabase. If you shrink a database, then it grows back to the
> size it was before the shrink and then you shrink it again, the
> process can unnecessarily waste a lot of resources as well as
> contributing to OS level fragmentation. You can find additional
> information in this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> -Sue
> On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
> <robertatcbb@.discussions.microsoft.com> wrote:
to[vbcol=seagreen]
also[vbcol=seagreen]
that
>
Maintinence Jobs vs Defag Jobs
Quick question, I have a maintinence job that optimizes (reorganizes back to
original size my databases back to the original amount of free space). I also
have jobs that defrag specific tables.
Will the maintinence job defrag as well as reorgainize.
I would like to get rid of my defrag jobs and have one maintinence job that
does both.Yes. If you select Reorganize data and index pages in the maintenance
plans, it will execute dbcc dbreindex. If you select Remove unused
space from database files, it will execute dbcc shrinkdatabase.
Considerations in going this route is that dbreindex is your only
option in using a maintenance plan. There are different considerations
to take into account when determining whether to use dbreindex or
indexdefrag. You would want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
A consideration with shrinking is that it is a resource intensive
operation. Additionally, the maintenance plan will limit you to using
shrinkdatabase instead of shrinkfile. You have less control and
somewhat unpredictable individual file sizes when using
shrinkdatabase. If you shrink a database, then it grows back to the
size it was before the shrink and then you shrink it again, the
process can unnecessarily waste a lot of resources as well as
contributing to OS level fragmentation. You can find additional
information in this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
<robertatcbb@.discussions.microsoft.com> wrote:
>Quick question, I have a maintinence job that optimizes (reorganizes back to
>original size my databases back to the original amount of free space). I also
>have jobs that defrag specific tables.
>Will the maintinence job defrag as well as reorgainize.
>I would like to get rid of my defrag jobs and have one maintinence job that
>does both.|||Another consideration is that shrinking introduces logical fragmentation
that dbcc dbreindex removes - if you reindex then shrink you're shooting
yourself in the foot...
You should ask yourself why you have to shrink repeatedly - the reason the
db size grows is that SQL needs the extra space. If the db size grows and
then remains relatively static, you'd do best to leave it alone otherwise
you're wasting resources as Sue says.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:knfvn0tq38v3a905ff208i5u134m35top5@.4ax.com...
> Yes. If you select Reorganize data and index pages in the maintenance
> plans, it will execute dbcc dbreindex. If you select Remove unused
> space from database files, it will execute dbcc shrinkdatabase.
> Considerations in going this route is that dbreindex is your only
> option in using a maintenance plan. There are different considerations
> to take into account when determining whether to use dbreindex or
> indexdefrag. You would want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> A consideration with shrinking is that it is a resource intensive
> operation. Additionally, the maintenance plan will limit you to using
> shrinkdatabase instead of shrinkfile. You have less control and
> somewhat unpredictable individual file sizes when using
> shrinkdatabase. If you shrink a database, then it grows back to the
> size it was before the shrink and then you shrink it again, the
> process can unnecessarily waste a lot of resources as well as
> contributing to OS level fragmentation. You can find additional
> information in this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> -Sue
> On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
> <robertatcbb@.discussions.microsoft.com> wrote:
> >Quick question, I have a maintinence job that optimizes (reorganizes back
to
> >original size my databases back to the original amount of free space). I
also
> >have jobs that defrag specific tables.
> >
> >Will the maintinence job defrag as well as reorgainize.
> >
> >I would like to get rid of my defrag jobs and have one maintinence job
that
> >does both.
>
original size my databases back to the original amount of free space). I also
have jobs that defrag specific tables.
Will the maintinence job defrag as well as reorgainize.
I would like to get rid of my defrag jobs and have one maintinence job that
does both.Yes. If you select Reorganize data and index pages in the maintenance
plans, it will execute dbcc dbreindex. If you select Remove unused
space from database files, it will execute dbcc shrinkdatabase.
Considerations in going this route is that dbreindex is your only
option in using a maintenance plan. There are different considerations
to take into account when determining whether to use dbreindex or
indexdefrag. You would want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
A consideration with shrinking is that it is a resource intensive
operation. Additionally, the maintenance plan will limit you to using
shrinkdatabase instead of shrinkfile. You have less control and
somewhat unpredictable individual file sizes when using
shrinkdatabase. If you shrink a database, then it grows back to the
size it was before the shrink and then you shrink it again, the
process can unnecessarily waste a lot of resources as well as
contributing to OS level fragmentation. You can find additional
information in this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
<robertatcbb@.discussions.microsoft.com> wrote:
>Quick question, I have a maintinence job that optimizes (reorganizes back to
>original size my databases back to the original amount of free space). I also
>have jobs that defrag specific tables.
>Will the maintinence job defrag as well as reorgainize.
>I would like to get rid of my defrag jobs and have one maintinence job that
>does both.|||Another consideration is that shrinking introduces logical fragmentation
that dbcc dbreindex removes - if you reindex then shrink you're shooting
yourself in the foot...
You should ask yourself why you have to shrink repeatedly - the reason the
db size grows is that SQL needs the extra space. If the db size grows and
then remains relatively static, you'd do best to leave it alone otherwise
you're wasting resources as Sue says.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:knfvn0tq38v3a905ff208i5u134m35top5@.4ax.com...
> Yes. If you select Reorganize data and index pages in the maintenance
> plans, it will execute dbcc dbreindex. If you select Remove unused
> space from database files, it will execute dbcc shrinkdatabase.
> Considerations in going this route is that dbreindex is your only
> option in using a maintenance plan. There are different considerations
> to take into account when determining whether to use dbreindex or
> indexdefrag. You would want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> A consideration with shrinking is that it is a resource intensive
> operation. Additionally, the maintenance plan will limit you to using
> shrinkdatabase instead of shrinkfile. You have less control and
> somewhat unpredictable individual file sizes when using
> shrinkdatabase. If you shrink a database, then it grows back to the
> size it was before the shrink and then you shrink it again, the
> process can unnecessarily waste a lot of resources as well as
> contributing to OS level fragmentation. You can find additional
> information in this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> -Sue
> On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
> <robertatcbb@.discussions.microsoft.com> wrote:
> >Quick question, I have a maintinence job that optimizes (reorganizes back
to
> >original size my databases back to the original amount of free space). I
also
> >have jobs that defrag specific tables.
> >
> >Will the maintinence job defrag as well as reorgainize.
> >
> >I would like to get rid of my defrag jobs and have one maintinence job
that
> >does both.
>
Maintinence Jobs vs Defag Jobs
Quick question, I have a maintinence job that optimizes (reorganizes back to
original size my databases back to the original amount of free space). I als
o
have jobs that defrag specific tables.
Will the maintinence job defrag as well as reorgainize.
I would like to get rid of my defrag jobs and have one maintinence job that
does both.Yes. If you select Reorganize data and index pages in the maintenance
plans, it will execute dbcc dbreindex. If you select Remove unused
space from database files, it will execute dbcc shrinkdatabase.
Considerations in going this route is that dbreindex is your only
option in using a maintenance plan. There are different considerations
to take into account when determining whether to use dbreindex or
indexdefrag. You would want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
A consideration with shrinking is that it is a resource intensive
operation. Additionally, the maintenance plan will limit you to using
shrinkdatabase instead of shrinkfile. You have less control and
somewhat unpredictable individual file sizes when using
shrinkdatabase. If you shrink a database, then it grows back to the
size it was before the shrink and then you shrink it again, the
process can unnecessarily waste a lot of resources as well as
contributing to OS level fragmentation. You can find additional
information in this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
<robertatcbb@.discussions.microsoft.com> wrote:
>Quick question, I have a maintinence job that optimizes (reorganizes back t
o
>original size my databases back to the original amount of free space). I al
so
>have jobs that defrag specific tables.
>Will the maintinence job defrag as well as reorgainize.
>I would like to get rid of my defrag jobs and have one maintinence job that
>does both.|||Another consideration is that shrinking introduces logical fragmentation
that dbcc dbreindex removes - if you reindex then shrink you're shooting
yourself in the foot...
You should ask yourself why you have to shrink repeatedly - the reason the
db size grows is that SQL needs the extra space. If the db size grows and
then remains relatively static, you'd do best to leave it alone otherwise
you're wasting resources as Sue says.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:knfvn0tq38v3a905ff208i5u134m35top5@.
4ax.com...
> Yes. If you select Reorganize data and index pages in the maintenance
> plans, it will execute dbcc dbreindex. If you select Remove unused
> space from database files, it will execute dbcc shrinkdatabase.
> Considerations in going this route is that dbreindex is your only
> option in using a maintenance plan. There are different considerations
> to take into account when determining whether to use dbreindex or
> indexdefrag. You would want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> A consideration with shrinking is that it is a resource intensive
> operation. Additionally, the maintenance plan will limit you to using
> shrinkdatabase instead of shrinkfile. You have less control and
> somewhat unpredictable individual file sizes when using
> shrinkdatabase. If you shrink a database, then it grows back to the
> size it was before the shrink and then you shrink it again, the
> process can unnecessarily waste a lot of resources as well as
> contributing to OS level fragmentation. You can find additional
> information in this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> -Sue
> On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
> <robertatcbb@.discussions.microsoft.com> wrote:
>
to[vbcol=seagreen]
also[vbcol=seagreen]
that[vbcol=seagreen]
>
original size my databases back to the original amount of free space). I als
o
have jobs that defrag specific tables.
Will the maintinence job defrag as well as reorgainize.
I would like to get rid of my defrag jobs and have one maintinence job that
does both.Yes. If you select Reorganize data and index pages in the maintenance
plans, it will execute dbcc dbreindex. If you select Remove unused
space from database files, it will execute dbcc shrinkdatabase.
Considerations in going this route is that dbreindex is your only
option in using a maintenance plan. There are different considerations
to take into account when determining whether to use dbreindex or
indexdefrag. You would want to check the following article:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
A consideration with shrinking is that it is a resource intensive
operation. Additionally, the maintenance plan will limit you to using
shrinkdatabase instead of shrinkfile. You have less control and
somewhat unpredictable individual file sizes when using
shrinkdatabase. If you shrink a database, then it grows back to the
size it was before the shrink and then you shrink it again, the
process can unnecessarily waste a lot of resources as well as
contributing to OS level fragmentation. You can find additional
information in this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
<robertatcbb@.discussions.microsoft.com> wrote:
>Quick question, I have a maintinence job that optimizes (reorganizes back t
o
>original size my databases back to the original amount of free space). I al
so
>have jobs that defrag specific tables.
>Will the maintinence job defrag as well as reorgainize.
>I would like to get rid of my defrag jobs and have one maintinence job that
>does both.|||Another consideration is that shrinking introduces logical fragmentation
that dbcc dbreindex removes - if you reindex then shrink you're shooting
yourself in the foot...
You should ask yourself why you have to shrink repeatedly - the reason the
db size grows is that SQL needs the extra space. If the db size grows and
then remains relatively static, you'd do best to leave it alone otherwise
you're wasting resources as Sue says.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:knfvn0tq38v3a905ff208i5u134m35top5@.
4ax.com...
> Yes. If you select Reorganize data and index pages in the maintenance
> plans, it will execute dbcc dbreindex. If you select Remove unused
> space from database files, it will execute dbcc shrinkdatabase.
> Considerations in going this route is that dbreindex is your only
> option in using a maintenance plan. There are different considerations
> to take into account when determining whether to use dbreindex or
> indexdefrag. You would want to check the following article:
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> A consideration with shrinking is that it is a resource intensive
> operation. Additionally, the maintenance plan will limit you to using
> shrinkdatabase instead of shrinkfile. You have less control and
> somewhat unpredictable individual file sizes when using
> shrinkdatabase. If you shrink a database, then it grows back to the
> size it was before the shrink and then you shrink it again, the
> process can unnecessarily waste a lot of resources as well as
> contributing to OS level fragmentation. You can find additional
> information in this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
> -Sue
> On Mon, 25 Oct 2004 09:59:05 -0700, "robert_at_cbb"
> <robertatcbb@.discussions.microsoft.com> wrote:
>
to[vbcol=seagreen]
also[vbcol=seagreen]
that[vbcol=seagreen]
>
Wednesday, March 7, 2012
Maintenance-Backup question
Hi,
I have a SQl database, when I view its properties from Enterprise manager, it's size is about 159 MB. I'm pretty sure I don't have that mucj data in it.
I know that Transaction log files grow over time, but I don't know how to control its growth? Is there a way to delete logs that are unecessary?
By backing up Transaction logs in a maintenance plan will the problem be solved?if you don't need transaction logs change your recovery model to Simple, that way the transaction logs will be truncated at checkpoint.
If you need transaction logs then you should be backing them up on a regular basis. After backup you can truncate the logs. See BOL and maintenance plan for syntax.|||Thanks for your reply,
I have read BOL's explanation of backups and recovery models, however I'm still confused.
I need to be able to have point in time recovery, therefor I've set the backup mode to FULL.
I have a maintenance plan that backs up both Log files and the database on a daily bases.
My confusion is mainly on the issue of Full versus other types of recovery models.
I want to have point in time recovery therefore I need the FULL Recovery mode, but at the same time I am able to determine at a specific point in time if I'm sure I don't need the transaction logs before that specific date and the previous daily backups won't be usfull, therefore I have came up with the below solution which I'm not sure if its correct:
"I will use the Daily log and data backup with the FULL Recovery mode. Then at any given time when I determine I won't need the Log files, I will temporary change the Rocovery mode to "SIMPLE" and manually backup the Log and data files, then after the manual back I will change the Recovery mode to FULL so that the daily scheduled backups will be in the FULL mode. This Way I get rid of the useless transaction logs and keep the new logs untill the next manual backup when I know that I'm safe."
Is this strategy correct?
Thanks|||Ah no.
Set your recovery model to full and DON'T change it unless you are doing big time data/server maintance.
My suggestion is to create a schedule of full backups and transaction log backups.
All transaction logs can be deleted AFTER a full backup is again taken.
Lets say to make a full backup each night at 01:30. Your system doesn't see much activity untill 07:00, is failry heavily used till 19:00 and little activity till 01:30 the next morning. I would take a log dump at 07:00, 19:00 and maybe 20:00. Between 07:00 and 19:00 you might want to take a log dump every hour of half hour. Regardless of the number of log dumps taken you can get rid of them after the next sucessful full backup.
You can specify al this in the maintance plan.
Think about this and poste back.|||Also, lookup "differential backups" in BOL, another good example of a backup strategy.|||A log dump is what I wanted to do, how do I specify a log dump?
Is a log dump another term for backing up transactions logs?
Originally posted by Paul Young
Ah no.
Set your recovery model to full and DON'T change it unless you are doing big time data/server maintance.
My suggestion is to create a schedule of full backups and transaction log backups.
All transaction logs can be deleted AFTER a full backup is again taken.
Lets say to make a full backup each night at 01:30. Your system doesn't see much activity untill 07:00, is failry heavily used till 19:00 and little activity till 01:30 the next morning. I would take a log dump at 07:00, 19:00 and maybe 20:00. Between 07:00 and 19:00 you might want to take a log dump every hour of half hour. Regardless of the number of log dumps taken you can get rid of them after the next sucessful full backup.
You can specify al this in the maintance plan.
Think about this and poste back.|||After backing up, use dbcc shrink file to shrink log file. Since DBCC shrinkfile only shrink from the end of the log file, sometime you have to intentionally do some operations to move the log to the start of the log file. Normally, after two times of backup and dbcc shrink, the log file will be shrinked dramatiscally.
Search google for Andrew Zanevsky, he wrote a good article and script of how to autamatically do the above steps.
Here's header of this script.
use master
go
if object_id( 'sp_force_shrink_log' ) is not null drop proc sp_force_shrink_log
go
create proc sp_force_shrink_log
/*
************************************************** ***********
Name: sp_force_shrink_log
Description:
Shrink transaction log of the current database in SQL Server 7.0.
Switch context to proper db to execute.
Usage: exec sp_force_shrink_log <target_percent>, <target MB>, <iterations>, <backup options>
exec pubs..sp_force_shrink_log
Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000
zanevsky@.azdatabases.com|||Originally posted by Sia
A log dump is what I wanted to do, how do I specify a log dump?
Is a log dump another term for backing up transactions logs?
Yes, refer to BOL for Tlog backups.|||Sia, finally found the article in BOL... look up "transaction logs, truncating" This should help you out.
Yes, log dump = backing up transaction log.
You will need to follow jzhu suggestion on shrinking the log files if yo uhave auto shrink turned off. IMNotSoHO I would wathc the log files and only shrink is space were a problem. If you consistantly need 1 gig of log space why go through the cycle of shrinking and growing? I shrink my logs only once a month.|||Thanks for your replay Paul.
I think I got it!|||Paul,
Thanks for your recent replys.
I have scheduled a Database and Log backup every day at 1 and 2 am.
The thing is that, before I do the Log backup my database had the following properties:
Space alloacted for data file was 50MB
Space alloacted for Log file was about 100 MB
availble space was about 70MB.
The I did a manual database and Log backup which resulted in the following db properties
Space alloacted for data file =50MB
Space alloacted for Log file =50MB
availble space= 100MB
Its now about 2 days that I'm doing the Log and Data backup using a scheduled maintenance plan.
I have the following DB Properties:
Space alloacted for data file =50MB
Space alloacted for Log file =2 MB
availble space= 14MB
=>My questions:
Why did the Log File size reduced dramatically after 2 backups with the maintenance plan but was not reduced that much when I did the first 2 backups manually.
Why is the "availble space" reduced!
My guess is that the availble space is calculated using the allocated space and since I have autogrowth it woud increase itself when the file reaches the limit.
I would appreciate if you let me know I'm on the right track or not.
Thanks
Originally posted by Sia
Thanks for your reply,
I have read BOL's explanation of backups and recovery models, however I'm still confused.
I need to be able to have point in time recovery, therefor I've set the backup mode to FULL.
I have a maintenance plan that backs up both Log files and the database on a daily bases.
My confusion is mainly on the issue of Full versus other types of recovery models.
I want to have point in time recovery therefore I need the FULL Recovery mode, but at the same time I am able to determine at a specific point in time if I'm sure I don't need the transaction logs before that specific date and the previous daily backups won't be usfull, therefore I have came up with the below solution which I'm not sure if its correct:
"I will use the Daily log and data backup with the FULL Recovery mode. Then at any given time when I determine I won't need the Log files, I will temporary change the Rocovery mode to "SIMPLE" and manually backup the Log and data files, then after the manual back I will change the Recovery mode to FULL so that the daily scheduled backups will be in the FULL mode. This Way I get rid of the useless transaction logs and keep the new logs untill the next manual backup when I know that I'm safe."
Is this strategy correct?
Thanks|||Okay, just because you truncated the log does not mean you will automatically reduce the size of the log file. When you dup the log file you are backing up all COMMITED transactions and then removing all the transactions you just backed up. There may have been several long running un-commited transactions still in the log. These transaction many cover several segmants in the log file and there for the log file can not be shrunk. It's kind of like a fragmented hard drive.
As for your 2nd question, your are on the right track. I have read all about this one in BOL but it gave me a head ache. Available space depens on the total size of db and log space and how much of each is used.
Bottom line is if you see your new maintance plan is working for you try it out for a month, you will probably need to make a few adjustments but you won't know till you let it run for a while|||Thank you for your help!
Originally posted by Paul Young
Okay, just because you truncated the log does not mean you will automatically reduce the size of the log file. When you dup the log file you are backing up all COMMITED transactions and then removing all the transactions you just backed up. There may have been several long running un-commited transactions still in the log. These transaction many cover several segmants in the log file and there for the log file can not be shrunk. It's kind of like a fragmented hard drive.
As for your 2nd question, your are on the right track. I have read all about this one in BOL but it gave me a head ache. Available space depens on the total size of db and log space and how much of each is used.
Bottom line is if you see your new maintance plan is working for you try it out for a month, you will probably need to make a few adjustments but you won't know till you let it run for a while
I have a SQl database, when I view its properties from Enterprise manager, it's size is about 159 MB. I'm pretty sure I don't have that mucj data in it.
I know that Transaction log files grow over time, but I don't know how to control its growth? Is there a way to delete logs that are unecessary?
By backing up Transaction logs in a maintenance plan will the problem be solved?if you don't need transaction logs change your recovery model to Simple, that way the transaction logs will be truncated at checkpoint.
If you need transaction logs then you should be backing them up on a regular basis. After backup you can truncate the logs. See BOL and maintenance plan for syntax.|||Thanks for your reply,
I have read BOL's explanation of backups and recovery models, however I'm still confused.
I need to be able to have point in time recovery, therefor I've set the backup mode to FULL.
I have a maintenance plan that backs up both Log files and the database on a daily bases.
My confusion is mainly on the issue of Full versus other types of recovery models.
I want to have point in time recovery therefore I need the FULL Recovery mode, but at the same time I am able to determine at a specific point in time if I'm sure I don't need the transaction logs before that specific date and the previous daily backups won't be usfull, therefore I have came up with the below solution which I'm not sure if its correct:
"I will use the Daily log and data backup with the FULL Recovery mode. Then at any given time when I determine I won't need the Log files, I will temporary change the Rocovery mode to "SIMPLE" and manually backup the Log and data files, then after the manual back I will change the Recovery mode to FULL so that the daily scheduled backups will be in the FULL mode. This Way I get rid of the useless transaction logs and keep the new logs untill the next manual backup when I know that I'm safe."
Is this strategy correct?
Thanks|||Ah no.
Set your recovery model to full and DON'T change it unless you are doing big time data/server maintance.
My suggestion is to create a schedule of full backups and transaction log backups.
All transaction logs can be deleted AFTER a full backup is again taken.
Lets say to make a full backup each night at 01:30. Your system doesn't see much activity untill 07:00, is failry heavily used till 19:00 and little activity till 01:30 the next morning. I would take a log dump at 07:00, 19:00 and maybe 20:00. Between 07:00 and 19:00 you might want to take a log dump every hour of half hour. Regardless of the number of log dumps taken you can get rid of them after the next sucessful full backup.
You can specify al this in the maintance plan.
Think about this and poste back.|||Also, lookup "differential backups" in BOL, another good example of a backup strategy.|||A log dump is what I wanted to do, how do I specify a log dump?
Is a log dump another term for backing up transactions logs?
Originally posted by Paul Young
Ah no.
Set your recovery model to full and DON'T change it unless you are doing big time data/server maintance.
My suggestion is to create a schedule of full backups and transaction log backups.
All transaction logs can be deleted AFTER a full backup is again taken.
Lets say to make a full backup each night at 01:30. Your system doesn't see much activity untill 07:00, is failry heavily used till 19:00 and little activity till 01:30 the next morning. I would take a log dump at 07:00, 19:00 and maybe 20:00. Between 07:00 and 19:00 you might want to take a log dump every hour of half hour. Regardless of the number of log dumps taken you can get rid of them after the next sucessful full backup.
You can specify al this in the maintance plan.
Think about this and poste back.|||After backing up, use dbcc shrink file to shrink log file. Since DBCC shrinkfile only shrink from the end of the log file, sometime you have to intentionally do some operations to move the log to the start of the log file. Normally, after two times of backup and dbcc shrink, the log file will be shrinked dramatiscally.
Search google for Andrew Zanevsky, he wrote a good article and script of how to autamatically do the above steps.
Here's header of this script.
use master
go
if object_id( 'sp_force_shrink_log' ) is not null drop proc sp_force_shrink_log
go
create proc sp_force_shrink_log
/*
************************************************** ***********
Name: sp_force_shrink_log
Description:
Shrink transaction log of the current database in SQL Server 7.0.
Switch context to proper db to execute.
Usage: exec sp_force_shrink_log <target_percent>, <target MB>, <iterations>, <backup options>
exec pubs..sp_force_shrink_log
Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000
zanevsky@.azdatabases.com|||Originally posted by Sia
A log dump is what I wanted to do, how do I specify a log dump?
Is a log dump another term for backing up transactions logs?
Yes, refer to BOL for Tlog backups.|||Sia, finally found the article in BOL... look up "transaction logs, truncating" This should help you out.
Yes, log dump = backing up transaction log.
You will need to follow jzhu suggestion on shrinking the log files if yo uhave auto shrink turned off. IMNotSoHO I would wathc the log files and only shrink is space were a problem. If you consistantly need 1 gig of log space why go through the cycle of shrinking and growing? I shrink my logs only once a month.|||Thanks for your replay Paul.
I think I got it!|||Paul,
Thanks for your recent replys.
I have scheduled a Database and Log backup every day at 1 and 2 am.
The thing is that, before I do the Log backup my database had the following properties:
Space alloacted for data file was 50MB
Space alloacted for Log file was about 100 MB
availble space was about 70MB.
The I did a manual database and Log backup which resulted in the following db properties
Space alloacted for data file =50MB
Space alloacted for Log file =50MB
availble space= 100MB
Its now about 2 days that I'm doing the Log and Data backup using a scheduled maintenance plan.
I have the following DB Properties:
Space alloacted for data file =50MB
Space alloacted for Log file =2 MB
availble space= 14MB
=>My questions:
Why did the Log File size reduced dramatically after 2 backups with the maintenance plan but was not reduced that much when I did the first 2 backups manually.
Why is the "availble space" reduced!
My guess is that the availble space is calculated using the allocated space and since I have autogrowth it woud increase itself when the file reaches the limit.
I would appreciate if you let me know I'm on the right track or not.
Thanks
Originally posted by Sia
Thanks for your reply,
I have read BOL's explanation of backups and recovery models, however I'm still confused.
I need to be able to have point in time recovery, therefor I've set the backup mode to FULL.
I have a maintenance plan that backs up both Log files and the database on a daily bases.
My confusion is mainly on the issue of Full versus other types of recovery models.
I want to have point in time recovery therefore I need the FULL Recovery mode, but at the same time I am able to determine at a specific point in time if I'm sure I don't need the transaction logs before that specific date and the previous daily backups won't be usfull, therefore I have came up with the below solution which I'm not sure if its correct:
"I will use the Daily log and data backup with the FULL Recovery mode. Then at any given time when I determine I won't need the Log files, I will temporary change the Rocovery mode to "SIMPLE" and manually backup the Log and data files, then after the manual back I will change the Recovery mode to FULL so that the daily scheduled backups will be in the FULL mode. This Way I get rid of the useless transaction logs and keep the new logs untill the next manual backup when I know that I'm safe."
Is this strategy correct?
Thanks|||Okay, just because you truncated the log does not mean you will automatically reduce the size of the log file. When you dup the log file you are backing up all COMMITED transactions and then removing all the transactions you just backed up. There may have been several long running un-commited transactions still in the log. These transaction many cover several segmants in the log file and there for the log file can not be shrunk. It's kind of like a fragmented hard drive.
As for your 2nd question, your are on the right track. I have read all about this one in BOL but it gave me a head ache. Available space depens on the total size of db and log space and how much of each is used.
Bottom line is if you see your new maintance plan is working for you try it out for a month, you will probably need to make a few adjustments but you won't know till you let it run for a while|||Thank you for your help!
Originally posted by Paul Young
Okay, just because you truncated the log does not mean you will automatically reduce the size of the log file. When you dup the log file you are backing up all COMMITED transactions and then removing all the transactions you just backed up. There may have been several long running un-commited transactions still in the log. These transaction many cover several segmants in the log file and there for the log file can not be shrunk. It's kind of like a fragmented hard drive.
As for your 2nd question, your are on the right track. I have read all about this one in BOL but it gave me a head ache. Available space depens on the total size of db and log space and how much of each is used.
Bottom line is if you see your new maintance plan is working for you try it out for a month, you will probably need to make a few adjustments but you won't know till you let it run for a while
Maintenance Wizard
Hi
After running maintenance wizard, I getting a backup file that is the same
size like the DB but with .bak.
Is there a way to compress this file automaticly?
The original size is 11GB after compress the size is 700MB.
Any idea?
Than'x
ShayHi
You could compress the OS directory to save space, or if you wish to move
the backup elsewhere winrar has command line capabilities, so you could call
that as an extra step within the job. Products such as SQL Litespeed will
also do this for you http://www.quest.com/litespeed_for_sql_server/
John
"S" wrote:
> Hi
> After running maintenance wizard, I getting a backup file that is the same
> size like the DB but with .bak.
> Is there a way to compress this file automaticly?
> The original size is 11GB after compress the size is 700MB.
> Any idea?
> Than'x
> Shay
>
>
After running maintenance wizard, I getting a backup file that is the same
size like the DB but with .bak.
Is there a way to compress this file automaticly?
The original size is 11GB after compress the size is 700MB.
Any idea?
Than'x
ShayHi
You could compress the OS directory to save space, or if you wish to move
the backup elsewhere winrar has command line capabilities, so you could call
that as an extra step within the job. Products such as SQL Litespeed will
also do this for you http://www.quest.com/litespeed_for_sql_server/
John
"S" wrote:
> Hi
> After running maintenance wizard, I getting a backup file that is the same
> size like the DB but with .bak.
> Is there a way to compress this file automaticly?
> The original size is 11GB after compress the size is 700MB.
> Any idea?
> Than'x
> Shay
>
>
Subscribe to:
Posts (Atom)