Archiving is Not reducing DB Size

I created few jobs that would archive the production DB and delete the archived data...
but it looks like the DB size is not reducing!!! Some times it looks like the size has increased!!

I think this is because of the log file size has increaded by the DELETE operations...But what can I do for this?

Please Help!!Action\All Tasks\Shrink Database in EM
or
DBCC SHRINKDATABASE in QA

Archiving database for reporting

Hi
SSIS can be a good choice.
Also
>also keep about 1 year
> of data in archive database.
DELETE FROM tbl WHERE dt <DATEADD(Year,-1,GETDATE())

> I must keep data in production database about
> two months, b
Create a job and schedule it on day period
I must keep data in production database about
INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >='19000101' AND
dt<=DATEADD(month,-2,GETDATE())

>Additionally two
> months old data in the production database can change (delete, update)
> and that also must be reflected in archive database.
Here, I'd suggest you to create a trigger or (take a look at OUTPUT clause)
and flag or whatever modified data
"OgnjenT" <OgnjenT@.gmail.com> wrote in message
news:3f74847e-97c1-4a69-bbb8-17813ccc2cbb@.u10g2000prn.googlegroups.com...
> HI,
> I have a database on sql server 2005 ent edition with about 300 000
> new records every day. I must keep data in production database about
> two months, but there are a lot of reporting activities which are
> interrupting normal functioning of production server and there is also
> requirement to query historical data(about 1 year) with reports. I
> must add new data to the archive database every day. Additionally two
> months old data in the production database can change (delete, update)
> and that also must be reflected in archive database. So basically I
> must insert new data, update changed date and delete deleted data from
> production database into archive database but also keep about 1 year
> of data in archive database. Then I can separately optimize archive
> database for reporting. What is the best way to satisfy all that
> requirements. I am thinking about SSIS.
On Feb 7, 9:03Xam, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> SSIS can be a good choice.
> Also
>
> DELETE FROM tbl WHERE dt <DATEADD(Year,-1,GETDATE())
>
> X X Create a job and schedule it on day period
> XI must keep data in production database about
> INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >='19000101' AND
> dt<=DATEADD(month,-2,GETDATE())
>
> Here, I'd suggest Xyou to create a trigger or (take a look at OUTPUT clause)
> and flag or whatever modified data
> "OgnjenT" <Ognj...@.gmail.com> wrote in message
> news:3f74847e-97c1-4a69-bbb8-17813ccc2cbb@.u10g2000prn.googlegroups.com...
>
>
> - Show quoted text -
Problem with triger is when I clean data older then two month it will
delete data in archive database too. Also I must then put the triger
on the other tables because of the referential integrity in the
archive database.

>INSERT INTO arch. tbl (..) SELECT ... FROM prod WHERE dt >='19000101' AND
>dt<=DATEADD(month,-2,GETDATE())
I can't do that because I must have to synchronize production and
archive database every day because every night I mast have a dozen of
reports.
I decided to have archive database because of different way of queries
for normal processing and for reporting and I don't wont reporting
have so mutch influence on normaln work. So it is not only archiving
data but creating separate database for reporting so I can put some
more indexes, indexed views and in the same time my insert and updates
will still be fast in the production database. Also production server
doesn't have to hold data older than two months so it is better to
clean it so my queries woold be faster.
Is it maybe ok to delete data older than two months from archive
database every night and insert that data from production database. It
seems to me that it will be faster than check every row in production
database and then update archive database if row is updated, delete if
deleted. The esiest of course is to insert new rows.
Maybe before that bulk delete and insert it wood be smart to drop all
indexes, indexed views and constraint and recreate it after. But I
must do all that for about 15 minutes.

Archive SQL Server Logs..?

Hi Folks
is anyone knows that how can i archive(delete) log file for current date or
specific date'?
Thanx in advance
regards
--
Message posted via http://www.sqlmonster.comHi,
Did you mean SQL Server error logs or Tranasction log backups?
If it is Error log, sql server by itself keep only 6+1 (current) copies by
default. The old files will be deleted automatically.
If it is Transaction log backup files, then use database maintenance plan to
fix a archival day. So as old file(s) will be deleted automatically.
Thanks
Hari
SQL Server MVP
"sonny singh via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:fa2cf9e684d741419c1094db51a9fea5@.SQLMonster.com...
> Hi Folks
> is anyone knows that how can i archive(delete) log file for current date
> or
> specific date'?
> Thanx in advance
> regards
> --
> Message posted via http://www.sqlmonster.com|||Hi Hari
You can change the number of error logs maintained by editing the Registry
for any instance. For the default instance, find the key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer and edit it by
adding a new value. Define the value with the name NumErrorLogs, and the
type REG_DWORD. Supply any initial value desired, but keep in mind that the
value you enter when editing the Registry will be in hexadecimal format.
For a named instance, you need to find the key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<SQL Server
Instance Name>\MSSQLServer. Again, use the Edit feature of the Registry to
add a new value with the name NumErrorLogs of type REG_DWORD, and supply an
initial value in hexadecimal format.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:Os1pMt2bFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Did you mean SQL Server error logs or Tranasction log backups?
> If it is Error log, sql server by itself keep only 6+1 (current) copies by
> default. The old files will be deleted automatically.
> If it is Transaction log backup files, then use database maintenance plan
> to fix a archival day. So as old file(s) will be deleted automatically.
> Thanks
> Hari
> SQL Server MVP
> "sonny singh via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
> message news:fa2cf9e684d741419c1094db51a9fea5@.SQLMonster.com...
>> Hi Folks
>> is anyone knows that how can i archive(delete) log file for current date
>> or
>> specific date'?
>> Thanx in advance
>> regards
>> --
>> Message posted via http://www.sqlmonster.com
>

Archive SQL Server Logs..?

Hi Folks
is anyone knows that how can i archive(delete) log file for current date or
specific date??
Thanx in advance
regards
Message posted via http://www.droptable.com
Hi,
Did you mean SQL Server error logs or Tranasction log backups?
If it is Error log, sql server by itself keep only 6+1 (current) copies by
default. The old files will be deleted automatically.
If it is Transaction log backup files, then use database maintenance plan to
fix a archival day. So as old file(s) will be deleted automatically.
Thanks
Hari
SQL Server MVP
"sonny singh via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:fa2cf9e684d741419c1094db51a9fea5@.droptable.co m...
> Hi Folks
> is anyone knows that how can i archive(delete) log file for current date
> or
> specific date??
> Thanx in advance
> regards
> --
> Message posted via http://www.droptable.com
|||Hi Hari
You can change the number of error logs maintained by editing the Registry
for any instance. For the default instance, find the key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer and edit it by
adding a new value. Define the value with the name NumErrorLogs, and the
type REG_DWORD. Supply any initial value desired, but keep in mind that the
value you enter when editing the Registry will be in hexadecimal format.
For a named instance, you need to find the key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<SQL Server
Instance Name>\MSSQLServer. Again, use the Edit feature of the Registry to
add a new value with the name NumErrorLogs of type REG_DWORD, and supply an
initial value in hexadecimal format.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:Os1pMt2bFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Did you mean SQL Server error logs or Tranasction log backups?
> If it is Error log, sql server by itself keep only 6+1 (current) copies by
> default. The old files will be deleted automatically.
> If it is Transaction log backup files, then use database maintenance plan
> to fix a archival day. So as old file(s) will be deleted automatically.
> Thanks
> Hari
> SQL Server MVP
> "sonny singh via droptable.com" <forum@.nospam.droptable.com> wrote in
> message news:fa2cf9e684d741419c1094db51a9fea5@.droptable.co m...
>

Archive SQL Server Logs..?

Hi Folks
is anyone knows that how can i archive(delete) log file for current date or
specific date'?
Thanx in advance
regards
Message posted via http://www.droptable.comHi,
Did you mean SQL Server error logs or Tranasction log backups?
If it is Error log, sql server by itself keep only 6+1 (current) copies by
default. The old files will be deleted automatically.
If it is Transaction log backup files, then use database maintenance plan to
fix a archival day. So as old file(s) will be deleted automatically.
Thanks
Hari
SQL Server MVP
"sonny singh via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:fa2cf9e684d741419c1094db51a9fea5@.SQ
droptable.com...
> Hi Folks
> is anyone knows that how can i archive(delete) log file for current date
> or
> specific date'?
> Thanx in advance
> regards
> --
> Message posted via http://www.droptable.com|||Hi Hari
You can change the number of error logs maintained by editing the Registry
for any instance. For the default instance, find the key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer and edit it by
adding a new value. Define the value with the name NumErrorLogs, and the
type REG_DWORD. Supply any initial value desired, but keep in mind that the
value you enter when editing the Registry will be in hexadecimal format.
For a named instance, you need to find the key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL Server\<SQL Server
Instance Name>\MSSQLServer. Again, use the Edit feature of the Registry to
add a new value with the name NumErrorLogs of type REG_DWORD, and supply an
initial value in hexadecimal format.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:Os1pMt2bFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Did you mean SQL Server error logs or Tranasction log backups?
> If it is Error log, sql server by itself keep only 6+1 (current) copies by
> default. The old files will be deleted automatically.
> If it is Transaction log backup files, then use database maintenance plan
> to fix a archival day. So as old file(s) will be deleted automatically.
> Thanks
> Hari
> SQL Server MVP
> "sonny singh via droptable.com" <forum@.nospam.droptable.com> wrote in
> message news:fa2cf9e684d741419c1094db51a9fea5@.SQ
droptable.com...
>

Application role help

Hi
I would like to generate report for each of my application role. Mean each
role has select /add/ update/delete / ... permissions with theri object
name. Like we see the permissions in properties tab of role.
Is their any stored procedure available internally or if we can write some
script ?
Thanks,
Manoj
See sp_helprotect in SQL Server Books Online.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Manoj" <Manoj@.discussions.microsoft.com> wrote in message
news:4712B2BA-9958-4583-83E0-5D92F5D38E2B@.microsoft.com...
> Hi
> I would like to generate report for each of my application role. Mean
each
> role has select /add/ update/delete / ... permissions with theri object
> name. Like we see the permissions in properties tab of role.
> Is their any stored procedure available internally or if we can write some
> script ?
> Thanks,
> Manoj

Application role help

Hi
I would like to generate report for each of my application role. Mean each
role has select /add/ update/delete / ... permissions with theri object
name. Like we see the permissions in properties tab of role.
Is their any stored procedure available internally or if we can write some
script ?
Thanks,
ManojSee sp_helprotect in SQL Server Books Online.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Manoj" <Manoj@.discussions.microsoft.com> wrote in message
news:4712B2BA-9958-4583-83E0-5D92F5D38E2B@.microsoft.com...
> Hi
> I would like to generate report for each of my application role. Mean
each
> role has select /add/ update/delete / ... permissions with theri object
> name. Like we see the permissions in properties tab of role.
> Is their any stored procedure available internally or if we can write some
> script ?
> Thanks,
> Manoj

Application insert\update\delete

Hi,
How can i know how many insert\update\delete done by application daily
in sql server 2000 in particaular database
is there any cmd to check the same in sql server 2000
Regards
AbhinavSeveral options:
1) Get a copy of ApexSQL Log and use it for auditing.
2) Purchase an auditing package (or write your own) using triggers to log
that an update/insert/delete was performed. You will not be able to
actually know the exact statement this way.
3) Modify the application code to log these statements prior to or just
after executing them.
4) Write a network packet interogator and disassemble all sql server bound
packets and check for appropriate statements and log them.
Some of these are easier and more efficient than others!! :-))
--
TheSQLGuru
President
Indicium Resources, Inc.
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1188302835.786870.105940@.r23g2000prd.googlegroups.com...
> Hi,
> How can i know how many insert\update\delete done by application daily
> in sql server 2000 in particaular database
> is there any cmd to check the same in sql server 2000
> Regards
> Abhinav
>

Anyway to recover delete stored proc?

Folks.

I screwed up big time, I deleted a very long and smart stored proc (pls
don't ask how).

Is there anyway I can recover it?

Any advice appreciated.

Tada.KoliPoki (rayone@.gmail.com) writes:
> I screwed up big time, I deleted a very long and smart stored proc (pls
> don't ask how).
> Is there anyway I can recover it?

Do you have a backup of the database? Or do you run the database with
full or bulk-logged recovery? In that case you might be able to.

If you don't have any backup and run with simple recovery, the procedure
has left for outer space.

Generally, all source code should be under version control. See the
database as the place where you have the binary representation of
the source.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

anything wrong with this trigger?

Hello,
I'm trying to create a trigger that will log to the eventviewer whenever
there is an update, delete, or insert statement made to the employee table.
However, I'm UNABLE to run any INSERT or DELETE statement into the employee
table when I have the severity level from 10-16 from the RAISERROR function.
I can run update just fine with the same severity level. Any idea what could
be causing this?
Thx
CREATE TRIGGER dbo.Audit ON dbo.Employee
FOR UPDATE, INSERT, DELETE
AS
BEGIN
DECLARE @.@.USERNAME varchar(30)
SET @.@.USERNAME = CURRENT_USER
RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
with log
END
I don't think RAISERROR is the right function for your requirements.
Consider using xp_logevent. More info on this at:
http://msdn.microsoft.com/library/de...aa-sz_6dmc.asp
With RAISERROR, you are unnecessarily raising an error for no apparent
reason. All you wanted was to log that an insert/update/delete occured.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>
|||I would also suggest changing the name of your local variable from
@.@.username ( which implies global, but is not) to @.username...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>

anything wrong with this trigger?

Hello,
I'm trying to create a trigger that will log to the eventviewer whenever
there is an update, delete, or insert statement made to the employee table.
However, I'm UNABLE to run any INSERT or DELETE statement into the employee
table when I have the severity level from 10-16 from the RAISERROR function.
I can run update just fine with the same severity level. Any idea what could
be causing this?
Thx
CREATE TRIGGER dbo.Audit ON dbo.Employee
FOR UPDATE, INSERT, DELETE
AS
BEGIN
DECLARE @.@.USERNAME varchar(30)
SET @.@.USERNAME = CURRENT_USER
RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
with log
ENDI don't think RAISERROR is the right function for your requirements.
Consider using xp_logevent. More info on this at:
http://msdn.microsoft.com/library/d.../>
sz_6dmc.asp
With RAISERROR, you are unnecessarily raising an error for no apparent
reason. All you wanted was to log that an insert/update/delete occured.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>|||I would also suggest changing the name of your local variable from
@.@.username ( which implies global, but is not) to @.username...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>

anything wrong with this trigger?

Hello,
I'm trying to create a trigger that will log to the eventviewer whenever
there is an update, delete, or insert statement made to the employee table.
However, I'm UNABLE to run any INSERT or DELETE statement into the employee
table when I have the severity level from 10-16 from the RAISERROR function.
I can run update just fine with the same severity level. Any idea what could
be causing this?
Thx
CREATE TRIGGER dbo.Audit ON dbo.Employee
FOR UPDATE, INSERT, DELETE
AS
BEGIN
DECLARE @.@.USERNAME varchar(30)
SET @.@.USERNAME = CURRENT_USER
RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
with log
ENDI don't think RAISERROR is the right function for your requirements.
Consider using xp_logevent. More info on this at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_6dmc.asp
With RAISERROR, you are unnecessarily raising an error for no apparent
reason. All you wanted was to log that an insert/update/delete occured.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>|||I would also suggest changing the name of your local variable from
@.@.username ( which implies global, but is not) to @.username...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Richard" <richardtran@.hotmail.com> wrote in message
news:OsSW3tGZEHA.3420@.TK2MSFTNGP12.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that will log to the eventviewer whenever
> there is an update, delete, or insert statement made to the employee
table.
> However, I'm UNABLE to run any INSERT or DELETE statement into the
employee
> table when I have the severity level from 10-16 from the RAISERROR
function.
> I can run update just fine with the same severity level. Any idea what
could
> be causing this?
>
> Thx
>
> CREATE TRIGGER dbo.Audit ON dbo.Employee
> FOR UPDATE, INSERT, DELETE
> AS
> BEGIN
> DECLARE @.@.USERNAME varchar(30)
> SET @.@.USERNAME = CURRENT_USER
> RAISERROR ('Employee table has been updated by: %s', 15, 1, @.@.USERNAME)
> with log
> END
>