sql server tools: archive

Showing posts with label archive. Show all posts

Tuesday, March 27, 2012

archiving tables

We have some fast growing tables in our SQL Server 2000 db
which content 13 million records already. So, we going to
periodically archive them base on datetime field and allow
user to retrieve archive data via our application by
checking corresponding checkbox. The problem is that, we
do not know what to do with FK's. How to re-create FK's
after archiving to restore referal integrity.
Is there some standard solution?
Any comments will be appreciated.
Merry chistmas and happy new year to everybody.Vitalik
http://vyaskn.tripod.com/sql_archive_data.htm
"vitaliyk" <vitaliykrasner@.hotmail.com> wrote in message
news:08ee01c3c964$5faa1340$a401280a@.phx.gbl...
> We have some fast growing tables in our SQL Server 2000 db
> which content 13 million records already. So, we going to
> periodically archive them base on datetime field and allow
> user to retrieve archive data via our application by
> checking corresponding checkbox. The problem is that, we
> do not know what to do with FK's. How to re-create FK's
> after archiving to restore referal integrity.
> Is there some standard solution?
> Any comments will be appreciated.
> Merry chistmas and happy new year to everybody.

Archiving Problem

Hi!
The SQL Server Express run several versions of an Archive file, such as
Archive #1, etc. under 'SQL Server Logs'.
What is generating these archives? Also, How can I see what has been
archived? Can I stop the server from doing this or needed for to operate?
Thank you very much!
-BahmanHI
"Bahman" wrote:
> Hi!
> The SQL Server Express run several versions of an Archive file, such as
> Archive #1, etc. under 'SQL Server Logs'.
> What is generating these archives? Also, How can I see what has been
> archived? Can I stop the server from doing this or needed for to operate?
> Thank you very much!
> -Bahman
>
These are old versions of the ERRORLOG file. A new ERRORLOG is created when
you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG.1
ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
will have the date and time that the log goes up to. More information is in
Books online.
John|||John,
Thanks a million. I guess the word archive was throwing me off. I was
looking in the wrong place. Somehow I thought it is archiving, so that I can
restore from it. But no info on that anywhere. Thank you very much for your
help.
-Bahman
"John Bell" wrote:
> HI
> "Bahman" wrote:
> > Hi!
> >
> > The SQL Server Express run several versions of an Archive file, such as
> > Archive #1, etc. under 'SQL Server Logs'.
> >
> > What is generating these archives? Also, How can I see what has been
> > archived? Can I stop the server from doing this or needed for to operate?
> >
> > Thank you very much!
> >
> > -Bahman
> >
> These are old versions of the ERRORLOG file. A new ERRORLOG is created when
> you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG.1
> ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
> will have the date and time that the log goes up to. More information is in
> Books online.
> John
>sql

Archiving Problem

Hi!
The SQL Server Express run several versions of an Archive file, such as
Archive #1, etc. under 'SQL Server Logs'.
What is generating these archives? Also, How can I see what has been
archived? Can I stop the server from doing this or needed for to operate?
Thank you very much!
-Bahman
HI
"Bahman" wrote:

> Hi!
> The SQL Server Express run several versions of an Archive file, such as
> Archive #1, etc. under 'SQL Server Logs'.
> What is generating these archives? Also, How can I see what has been
> archived? Can I stop the server from doing this or needed for to operate?
> Thank you very much!
> -Bahman
>
These are old versions of the ERRORLOG file. A new ERRORLOG is created when
you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG.1
ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
will have the date and time that the log goes up to. More information is in
Books online.
John
|||John,
Thanks a million. I guess the word archive was throwing me off. I was
looking in the wrong place. Somehow I thought it is archiving, so that I can
restore from it. But no info on that anywhere. Thank you very much for your
help.
-Bahman
"John Bell" wrote:

> HI
> "Bahman" wrote:
>
> These are old versions of the ERRORLOG file. A new ERRORLOG is created when
> you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG.1
> ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
> will have the date and time that the log goes up to. More information is in
> Books online.
> John
>

Archiving Problem

Hi!
The SQL Server Express run several versions of an Archive file, such as
Archive #1, etc. under 'SQL Server Logs'.
What is generating these archives? Also, How can I see what has been
archived? Can I stop the server from doing this or needed for to operate?
Thank you very much!
-BahmanHI
"Bahman" wrote:

> Hi!
> The SQL Server Express run several versions of an Archive file, such as
> Archive #1, etc. under 'SQL Server Logs'.
> What is generating these archives? Also, How can I see what has been
> archived? Can I stop the server from doing this or needed for to operate?
> Thank you very much!
> -Bahman
>
These are old versions of the ERRORLOG file. A new ERRORLOG is created when
you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG.1
ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
will have the date and time that the log goes up to. More information is in
Books online.
John|||John,
Thanks a million. I guess the word archive was throwing me off. I was
looking in the wrong place. Somehow I thought it is archiving, so that I can
restore from it. But no info on that anywhere. Thank you very much for your
help.
-Bahman
"John Bell" wrote:

> HI
> "Bahman" wrote:
>
> These are old versions of the ERRORLOG file. A new ERRORLOG is created whe
n
> you restart SQL Server or run sp_cycle_errorlog. The file will be ERRORLOG
.1
> ERRORLOG.2 etc.. in the LOGS directory. The Displayed name in SSMS and EM
> will have the date and time that the log goes up to. More information is i
n
> Books online.
> John
>

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.

Archiving AS 2000 Database to a network backup server

WE are currently using the following to archive our AS 2000 OLAP cubes to the local directory on the server:

"C:\Program Files (x86)\Microsoft Analysis Services\Bin\msmdarch" /a ServerName"C:\Program Files (x86)\Microsoft Analysis Services\Data\DataMart" "DataMart" "c:\BackupFiles\DataMart.cab" It runs inside an agent job. It works fine.

I wanted to change it to archive to our standard backup server location. So I changed it to the following:

"C:\Program Files (x86)\Microsoft Analysis Services\Bin\msmdarch" /a ServerName"C:\Program Files (x86)\Microsoft Analysis Services\Data\DataMart" "DataMart" "\\10.0.50.115\Disk3\servers\SQL\ServerName\DataMart.cab". The job now fails & outputs nothing to the log file. The only thing it says when I view history on the job is : Executed as user: SqlAdmin. The step did not generate any output. Process Exit Code 1. The step failed.

At first I thought maybe AS 2000 couldn't archive over the network so I open up AS Manager & manually did an archive to the same network location. It was successfull. So that blew my theory.

I don't know why the job won't do the archive to the network location. Any ideas?

Thanks,

John

If you can manually run the command

then it's possible that the SqlAdmin user doesn't have write permissions to the share.

Adrian

|||

Adrian,

I forgot to mention that SQLAdmin does have privileges to the network backup location. We use this same login for all our SQL backups as well.

I wasn't ever able to get the command to run manually. I was able to manually run the archive process through Analysis manager. Don't know if that makes a difference or not.

Any other ideas?

Thanks,

JOhn

|||

Unless you have changed the default data directory, I think you have an incorrect "datamart" sub folder in your data directory parameter.

try removing it and see if the command works then.

"C:\Program Files (x86)\Microsoft Analysis Services\Bin\msmdarch" /a ServerName"C:\Program Files (x86)\Microsoft Analysis Services\Data\" "DataMart" "\\10.0.50.115\Disk3\servers\SQL\ServerName\DataMart.cab"

Also, where are you running Analysis Manager from? Judging by the (x86) in the path I would say that your AS2k server is 64bit and all the management tools (including msmdarch.exe) are 32 bit. The last recommendation I saw said to try to run these tools from a 32bit machine and not on the 64bit server. I don't know if that means it would not work, but it might be another option to explore.

|||

Darren,

I removed the extra "datamart" from the command but same result.

Sorry I didn't mention this earlier. I am running W2K3 64 bit with AS 2000 32 bit. I was running Analysis Manager on the 64 bit server when I was able to do the archive manually. WIth this command, I am running it under SQL 2005 EE 64 bit agent job.

I tried running the following command from our 32 bit test server but it had the same result:

"C:\Program Files\Microsoft Analysis Services\Bin\msmdarch" /a 64BitServer"C:\Program Files (x86)\Microsoft Analysis Services\Data\" "DataMart" "\\10.0.50.115\Disk3\servers\SQL\64BitServer\DataMart.cab"

Any other ideas?

Thanks,

John

|||

Hmm, running out of ideas.

Have you tried mapping a drive so that you are backing up to z:\... or something like that?

Have you tried specifying the other 2 optional parameter (the logfile and tempfolder)? The log file might give you some ideas, although it might be blank, I seem to remember having trouble like this once, but I can't remember how I resolved it.

Finally, if all else fails there was some documentation, I think in the operations guide whitepaper, on how to "manually" back up an AS2k server by taking a file back up of the data directory and the repository database.

|||

Darren,

A mapped drive seems to work. Although, I don't like using mapped drives on our servers but I may have to make an exception in this instance. Maybe I will have it archive locally & then execute a batch job that would xcopy the .cab file to the backup server.

I also tried adding the log & temp folder parameters but that did not make any difference. I still got the useless blank log file. Smile It seems like this should work or somewhere it would be documented that you can't use UNC paths for the archive process.

Thanks for the help,

JOhn

|||

Hello,

Enjoyed reading the thread, I had a thought that might be helpful:

You might try enabling UNC support for your command prompt. By default, Microsoft ships this flag turned off, but I've yet to have any trouble with it turned on. YMMV.

Microsoft KB156276 instructions for doing this:

Under the registry path:

HKEY_CURRENT_USER
\Software
\Microsoft
\Command Processor

add the value DisableUNCCheck REG_DWORD and set the value to 0 x 1 (Hex).

http://support.microsoft.com/kb/156276

I hope this helps you. Good luck...!

-ed2

|||

Ed,

Thanks for the thought. I added this to the registry & then ran my job but same result. It may need to have a reboot to get the change into effect. However, the KB article lists this as a bug for Win NT 4 & states that it would be fixed in a SP. So I would hope that this has been fixed in Windows 2003 server. I will leave it until after a reboot to see if it works or not.

THe batch job is working great btw.

John

|||

I'm sorry to hear this wasn't a fix... WIth only 8 months of mainstream support remaining on SQL Server 2000, it's hard to imagine this will get much attention, but you might try opening a call if you can justify the expense...

Read the KB article I sent you over again, and you'll find out that the fix is in Win2K, XP, and Win2K3. The KB article said that NT4 originally had no check for UNC pathing in the command prompt, and that caused problems for users who would launch Windows apps from the command prompt session, then close the command prompt and the pipeline to the exe file would become broken. To remedy this Microsoft changed CMD.EXE such that whenever you use a UNC path at the command line it produces an error message UNLESS you put in the registry entry from the KB article. So basically the fix was to decide that it was inappropriate to use UNC pathing at the command prompt. However, I think that once you understand the limitations (and know what not to do), the benefits gained by using the UNC pathing outweigh the risks.

In one of the earlier posts it sounded as though you might be running the job from SQL Server Agent. I've seen some odd behavior from that in some ways -- for example, I wasn't able to call an osql command from SQL Server Agent using xp_cmdshell (pointless as that might seem). So, if you haven't already done so, try the Windows Job Scheduler instead.

Best wishes,

Archiving AS 2000 Database to a network backup server

WE are currently using the following to archive our AS 2000 OLAP cubes to the local directory on the server:

I wanted to change it to archive to our standard backup server location. So I changed it to the following:

At first I thought maybe AS 2000 couldn't archive over the network so I open up AS Manager & manually did an archive to the same network location. It was successfull. So that blew my theory.

I don't know why the job won't do the archive to the network location. Any ideas?

Thanks,

John

If you can manually run the command

then it's possible that the SqlAdmin user doesn't have write permissions to the share.

Adrian

|||

Adrian,

I forgot to mention that SQLAdmin does have privileges to the network backup location. We use this same login for all our SQL backups as well.

I wasn't ever able to get the command to run manually. I was able to manually run the archive process through Analysis manager. Don't know if that makes a difference or not.

Any other ideas?

Thanks,

JOhn

|||

Unless you have changed the default data directory, I think you have an incorrect "datamart" sub folder in your data directory parameter.

try removing it and see if the command works then.

|||

Darren,

I removed the extra "datamart" from the command but same result.

I tried running the following command from our 32 bit test server but it had the same result:

Any other ideas?

Thanks,

John

|||

Hmm, running out of ideas.

Have you tried mapping a drive so that you are backing up to z:\... or something like that?

|||

Darren,

Thanks for the help,

JOhn

|||

Hello,

Enjoyed reading the thread, I had a thought that might be helpful:

You might try enabling UNC support for your command prompt. By default, Microsoft ships this flag turned off, but I've yet to have any trouble with it turned on. YMMV.

Microsoft KB156276 instructions for doing this:

Under the registry path:

HKEY_CURRENT_USER
\Software
\Microsoft
\Command Processor

add the value DisableUNCCheck REG_DWORD and set the value to 0 x 1 (Hex).

http://support.microsoft.com/kb/156276

I hope this helps you. Good luck...!

-ed2

|||

Ed,

THe batch job is working great btw.

John

|||

Best wishes,

Archiving AS 2000 Database to a network backup server

WE are currently using the following to archive our AS 2000 OLAP cubes to the local directory on the server:

I wanted to change it to archive to our standard backup server location. So I changed it to the following:

At first I thought maybe AS 2000 couldn't archive over the network so I open up AS Manager & manually did an archive to the same network location. It was successfull. So that blew my theory.

I don't know why the job won't do the archive to the network location. Any ideas?

Thanks,

John

If you can manually run the command

then it's possible that the SqlAdmin user doesn't have write permissions to the share.

Adrian

|||

Adrian,

I forgot to mention that SQLAdmin does have privileges to the network backup location. We use this same login for all our SQL backups as well.

I wasn't ever able to get the command to run manually. I was able to manually run the archive process through Analysis manager. Don't know if that makes a difference or not.

Any other ideas?

Thanks,

JOhn

|||

Unless you have changed the default data directory, I think you have an incorrect "datamart" sub folder in your data directory parameter.

try removing it and see if the command works then.

|||

Darren,

I removed the extra "datamart" from the command but same result.

I tried running the following command from our 32 bit test server but it had the same result:

Any other ideas?

Thanks,

John

|||

Hmm, running out of ideas.

Have you tried mapping a drive so that you are backing up to z:\... or something like that?

|||

Darren,

Thanks for the help,

JOhn

|||

Hello,

Enjoyed reading the thread, I had a thought that might be helpful:

You might try enabling UNC support for your command prompt. By default, Microsoft ships this flag turned off, but I've yet to have any trouble with it turned on. YMMV.

Microsoft KB156276 instructions for doing this:

Under the registry path:

HKEY_CURRENT_USER
\Software
\Microsoft
\Command Processor

add the value DisableUNCCheck REG_DWORD and set the value to 0 x 1 (Hex).

http://support.microsoft.com/kb/156276

I hope this helps you. Good luck...!

-ed2

|||

Ed,

THe batch job is working great btw.

John

|||

Best wishes,

Archiving a database

We currently have a database of about 2,213mb, what is the best way to
archive the data?
Thanks
Gavin...Use the SQL Server "maintenance plan" to schedule backup (
Expand "Management" -> Right click on "Database
Maintenance Plan" -> click "New Maintenance Plan".
(or)
Right click on the database name -> All Tasks -> Backup
database.
SQLVarad (MCDBA-1999,MCSE-1999)
>--Original Message--
>We currently have a database of about 2,213mb, what is
the best way to
>archive the data?
>Thanks
>Gavin...
>
>.
>

Archiving

What's the best way to archive tables with binary data? Flat files/bcp/DTS
may not work if need to import binary data(not a fixed length column) with
quotes etc. What I am thinking is to export to another database and back it
up/archive on a regular basis. That way it will be easy to restore when
needed. Any other options?
Thanks
BVRVyas has a great article on his web site about archiving.
http://vyaskn.tripod.com/
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>|||Here's the link: http://vyaskn.tripod.com/sql_archive_data.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ogj%23SXfKFHA.3332@.TK2MSFTNGP15.phx.gbl...
Vyas has a great article on his web site about archiving.
http://vyaskn.tripod.com/
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>|||Narayanan,
I saw your Link, it looks good but i think it may or may not work if the
orders for 6 months is more than 100,000.
What happens if the data is more than 100,000 as we have in a real-time
system.
my solution was (lets take 6 months period - 100,000 records)
If you have to leave 10,000 on live production database and remove the rest
to history database.
This is how I did and its working great.
1. Check the record count of the database based on date.
2. Use Chunking method - means copy 1000-5000 every time depending on the
load from the live database to temp archive database on the live server then
call another str proc to copy data from temp archive to remote archive
database server.
(reason being if network goes down btw live server/remote server [reason
unknown] you can still have data in temp not lost and can come back redo the
data.)
3. once copied to remote archive server, now come back delete that data from
the live database by "delete from live where seqno in temp archive database"
and later delete from temp archive. (seqno is a primary key field)
I go beyond this, once all the data has been copied, i check for the first
and last copied to archive server exists meaning it has successfully copied.
I wrote a program to do this, the program just calls 3 stored procedures and
str proc does all the above function.
Too much isn't it.
Arun
"Narayana Vyas Kondreddi" wrote:
> Here's the link: http://vyaskn.tripod.com/sql_archive_data.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Ogj%23SXfKFHA.3332@.TK2MSFTNGP15.phx.gbl...
>
> Vyas has a great article on his web site about archiving.
> http://vyaskn.tripod.com/
>
>
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> > What's the best way to archive tables with binary data? Flat
> files/bcp/DTS
> > may not work if need to import binary data(not a fixed length column) with
> > quotes etc. What I am thinking is to export to another database and back
> it
> > up/archive on a regular basis. That way it will be easy to restore when
> > needed. Any other options?
> >
> > Thanks
> > BVR
> >
> >
>
>

Archiving

After doing some archiving I have found that rather than freeing up the
space straight after the archive run, the database is freeing up space
slowly over a number of days. Can anybody explain this? I am presuming that
there is some background process cleaning up and freeing the space.
Gav> After doing some archiving I have found that rather than freeing up the
> space straight after the archive run, the database is freeing up space
> slowly over a number of days. Can anybody explain this? I am presuming
that
> there is some background process cleaning up and freeing the space.
Are you talking abou SQL Server and Backup command? Bakup is not freeing the
space. Take a look at the DBCC SHRINKDATABASE and DBCC SHRINKFILE commands
in Books OnLine if this is what you need.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.|||No we have a SQL SAP database. At the weekend we archived some SAP data (at
SAP level), the data has been deleted from the database but the free space
has not gone up in one go, it seems to free up a bit at a time each day. I
am not shrinking anything.
example. We delete 2Gb of data from the database. After deleteing the data
it appears as if only 100 MB has been deleted. But each day a further 300MB
free space appears until 2Gb worth of free space has appeared.
Note: Not actual figures just a explanation of what happens.
Gav
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OdLzKK4rDHA.1784@.TK2MSFTNGP09.phx.gbl...
> > After doing some archiving I have found that rather than freeing up the
> > space straight after the archive run, the database is freeing up space
> > slowly over a number of days. Can anybody explain this? I am presuming
> that
> > there is some background process cleaning up and freeing the space.
> Are you talking abou SQL Server and Backup command? Bakup is not freeing
the
> space. Take a look at the DBCC SHRINKDATABASE and DBCC SHRINKFILE commands
> in Books OnLine if this is what you need.
> --
> Dejan Sarka, SQL Server MVP
> Please reply only to the newsgroups.
>|||So you aren't talking about file sizes, but usage inside the files? If so, did you run DBCC
UPDATEUSAGE? Also, could possibly be a fragmentation issue.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Gav" <gavin.metcalfe@.portakabinnospam.com> wrote in message
news:bpirmq$a5q$1@.hercules.btinternet.com...
> No we have a SQL SAP database. At the weekend we archived some SAP data (at
> SAP level), the data has been deleted from the database but the free space
> has not gone up in one go, it seems to free up a bit at a time each day. I
> am not shrinking anything.
> example. We delete 2Gb of data from the database. After deleteing the data
> it appears as if only 100 MB has been deleted. But each day a further 300MB
> free space appears until 2Gb worth of free space has appeared.
> Note: Not actual figures just a explanation of what happens.
> Gav
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:OdLzKK4rDHA.1784@.TK2MSFTNGP09.phx.gbl...
> > > After doing some archiving I have found that rather than freeing up the
> > > space straight after the archive run, the database is freeing up space
> > > slowly over a number of days. Can anybody explain this? I am presuming
> > that
> > > there is some background process cleaning up and freeing the space.
> >
> > Are you talking abou SQL Server and Backup command? Bakup is not freeing
> the
> > space. Take a look at the DBCC SHRINKDATABASE and DBCC SHRINKFILE commands
> > in Books OnLine if this is what you need.
> >
> > --
> > Dejan Sarka, SQL Server MVP
> > Please reply only to the newsgroups.
> >
> >
>|||No I havn't. But since your post I have tested this on a non-production
server and it seems to do the trick. Thanks for your advice. :o)
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OiUJYq6rDHA.1196@.TK2MSFTNGP12.phx.gbl...
> So you aren't talking about file sizes, but usage inside the files? If so,
did you run DBCC
> UPDATEUSAGE? Also, could possibly be a fragmentation issue.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Gav" <gavin.metcalfe@.portakabinnospam.com> wrote in message
> news:bpirmq$a5q$1@.hercules.btinternet.com...
> > No we have a SQL SAP database. At the weekend we archived some SAP data
(at
> > SAP level), the data has been deleted from the database but the free
space
> > has not gone up in one go, it seems to free up a bit at a time each day.
I
> > am not shrinking anything.
> >
> > example. We delete 2Gb of data from the database. After deleteing the
data
> > it appears as if only 100 MB has been deleted. But each day a further
300MB
> > free space appears until 2Gb worth of free space has appeared.
> >
> > Note: Not actual figures just a explanation of what happens.
> >
> > Gav
> >
> > "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote
in
> > message news:OdLzKK4rDHA.1784@.TK2MSFTNGP09.phx.gbl...
> > > > After doing some archiving I have found that rather than freeing up
the
> > > > space straight after the archive run, the database is freeing up
space
> > > > slowly over a number of days. Can anybody explain this? I am
presuming
> > > that
> > > > there is some background process cleaning up and freeing the space.
> > >
> > > Are you talking abou SQL Server and Backup command? Bakup is not
freeing
> > the
> > > space. Take a look at the DBCC SHRINKDATABASE and DBCC SHRINKFILE
commands
> > > in Books OnLine if this is what you need.
> > >
> > > --
> > > Dejan Sarka, SQL Server MVP
> > > Please reply only to the newsgroups.
> > >
> > >
> >
> >
>

Sunday, March 25, 2012

Archiving

What's the best way to archive tables with binary data? Flat files/bcp/DTS
may not work if need to import binary data(not a fixed length column) with
quotes etc. What I am thinking is to export to another database and back it
up/archive on a regular basis. That way it will be easy to restore when
needed. Any other options?
Thanks
BVR
Vyas has a great article on his web site about archiving.
http://vyaskn.tripod.com/
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>
|||Here's the link: http://vyaskn.tripod.com/sql_archive_data.htm
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ogj%23SXfKFHA.3332@.TK2MSFTNGP15.phx.gbl...
Vyas has a great article on his web site about archiving.
http://vyaskn.tripod.com/
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>
|||Narayanan,
I saw your Link, it looks good but i think it may or may not work if the
orders for 6 months is more than 100,000.
What happens if the data is more than 100,000 as we have in a real-time
system.
my solution was (lets take 6 months period - 100,000 records)
If you have to leave 10,000 on live production database and remove the rest
to history database.
This is how I did and its working great.
1. Check the record count of the database based on date.
2. Use Chunking method - means copy 1000-5000 every time depending on the
load from the live database to temp archive database on the live server then
call another str proc to copy data from temp archive to remote archive
database server.
(reason being if network goes down btw live server/remote server [reason
unknown] you can still have data in temp not lost and can come back redo the
data.)
3. once copied to remote archive server, now come back delete that data from
the live database by "delete from live where seqno in temp archive database"
and later delete from temp archive. (seqno is a primary key field)
I go beyond this, once all the data has been copied, i check for the first
and last copied to archive server exists meaning it has successfully copied.
I wrote a program to do this, the program just calls 3 stored procedures and
str proc does all the above function.
Too much isn't it.
Arun
"Narayana Vyas Kondreddi" wrote:

> Here's the link: http://vyaskn.tripod.com/sql_archive_data.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Ogj%23SXfKFHA.3332@.TK2MSFTNGP15.phx.gbl...
>
> Vyas has a great article on his web site about archiving.
> http://vyaskn.tripod.com/
>
>
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> files/bcp/DTS
> it
>
>

Archiving

What's the best way to archive tables with binary data? Flat files/bcp/DTS
may not work if need to import binary data(not a fixed length column) with
quotes etc. What I am thinking is to export to another database and back it
up/archive on a regular basis. That way it will be easy to restore when
needed. Any other options?
Thanks
BVRVyas has a great article on his web site about archiving.
http://vyaskn.tripod.com/
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>|||Here's the link: http://vyaskn.tripod.com/sql_archive_data.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Ogj%23SXfKFHA.3332@.TK2MSFTNGP15.phx.gbl...
Vyas has a great article on his web site about archiving.
http://vyaskn.tripod.com/
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> What's the best way to archive tables with binary data? Flat
files/bcp/DTS
> may not work if need to import binary data(not a fixed length column) with
> quotes etc. What I am thinking is to export to another database and back
it
> up/archive on a regular basis. That way it will be easy to restore when
> needed. Any other options?
> Thanks
> BVR
>|||Narayanan,
I saw your Link, it looks good but i think it may or may not work if the
orders for 6 months is more than 100,000.
What happens if the data is more than 100,000 as we have in a real-time
system.
my solution was (lets take 6 months period - 100,000 records)
If you have to leave 10,000 on live production database and remove the rest
to history database.
This is how I did and its working great.
1. Check the record count of the database based on date.
2. Use Chunking method - means copy 1000-5000 every time depending on the
load from the live database to temp archive database on the live server then
call another str proc to copy data from temp archive to remote archive
database server.
(reason being if network goes down btw live server/remote server [reason
unknown] you can still have data in temp not lost and can come back redo the
data.)
3. once copied to remote archive server, now come back delete that data from
the live database by "delete from live where seqno in temp archive database"
and later delete from temp archive. (seqno is a primary key field)
I go beyond this, once all the data has been copied, i check for the first
and last copied to archive server exists meaning it has successfully copied.
I wrote a program to do this, the program just calls 3 stored procedures and
str proc does all the above function.
Too much isn't it.
Arun
"Narayana Vyas Kondreddi" wrote:

> Here's the link: http://vyaskn.tripod.com/sql_archive_data.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Ogj%23SXfKFHA.3332@.TK2MSFTNGP15.phx.gbl...
>
> Vyas has a great article on his web site about archiving.
> http://vyaskn.tripod.com/
>
>
> "Uhway" <vbhadharla@.sbcglobal.net> wrote in message
> news:OwmojqdKFHA.1476@.TK2MSFTNGP09.phx.gbl...
> files/bcp/DTS
> it
>
>

Archive Strategy

In our Inventory Control database we have tables that grow and grow and grow
as the database gets used. Tables like StockResults, Deliveries, StockCount
etc.
I want to archive the data into archive tables, which I can later moved to
another database, or even another server. This way I'll have current tables
and archived tables (eg. Deliveries and Deliveries_Archive).
Here are some considerations:
1. We will need to report on both the current tables and the archived tables
.
2. data that is archived might need to be put back into its current table.
What I'm after is some advice on what strategy to use.
Is there a framework or design for doing this that would help me ?
Do you have an archive strategy that works for you ?
Do you have a clever naming convention that you use ?
Any good articles on this ?
Any help would appreciated.
Thanks,
CraigCheck this link.. Hope this helps.
http://www.dbazine.com/sql/sql-articles/charran12|||Why do you feel it is necessary to archive the data?|||To keep the transactional database's size down
We want to do this because
1. speeds up backups
2. if we have a problem and need to restore, that will be quicker
3. we often backup the db and download it to a testing server to test new
functionality -- the smaller db will download quicker

Archive Stored Procedure

My SP looks like this that originally populates the record set.

CREATE PROCEDURE dbo.sp_insert_file

@.KBID int,
@.rev int,
@.filename nvarchar(50),
@.moddate datetime,
@.owner nvarchar(50),
@.author nvarchar(50)

INSERT INTO KBFile
(KBID, rev, filename, moddate, owner, author)
VALUES (@.KBID,@.rev,@.filename,@.moddate,@.owner, @.author)
GO

I need to have a stored procedure that will select a recordset based on KBID(PK) and archive the entire record set to another table. I then need to be able to UPDATE the individual records for this record set in the KBFile table.

Can someone point me in the right direction?

Used Trigger instead of SP

http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart2.asp

thanks,Arnie Rowland

http://forums.microsoft.com/technet/ShowPost.aspx?postid=1199319&isthread=true&siteid=17&authhash=bf39638b468336d0c6fe8e12045e034125b540e1&ticks=633064241797780343

Archive Stored Procedure

My SP looks like this that originally populates the record set.

CREATE PROCEDURE dbo.sp_insert_file

@.KBID int,
@.rev int,
@.filename nvarchar(50),
@.moddate datetime,
@.owner nvarchar(50),
@.author nvarchar(50)

INSERT INTO KBFile
(KBID, rev, filename, moddate, owner, author)
VALUES (@.KBID,@.rev,@.filename,@.moddate,@.owner, @.author)
GO

I am trying to create an 'audit' trail while maintaining previous records.

Can someone point me in the right direction?

What about using an update trigger ? You will be able to audit all change events (not only these from stored procedures)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Often, a TRIGGER is used for Audit trails since the TRIGGER execution cannot be avoided. Audit trails are more 'risky' when stored procedures are used.

You might find one of these references pointing to the use of TRIGGERS to be helpful:

Trigger -Audit Trail
http://www.nigelrivett.net/AuditTrailTrigger.html
http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart1.asp
http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart2.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp

sql

Archive Stored Procedure

My SP looks like this that originally populates the record set.

CREATE PROCEDURE dbo.sp_insert_file

@.KBID int,
@.rev int,
@.filename nvarchar(50),
@.moddate datetime,
@.owner nvarchar(50),
@.author nvarchar(50)

INSERT INTO KBFile
(KBID, rev, filename, moddate, owner, author)
VALUES (@.KBID,@.rev,@.filename,@.moddate,@.owner, @.author)
GO

I am trying to create an 'audit' trail while maintaining previous records.

Can someone point me in the right direction?

What about using an update trigger ? You will be able to audit all change events (not only these from stored procedures)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Often, a TRIGGER is used for Audit trails since the TRIGGER execution cannot be avoided. Audit trails are more 'risky' when stored procedures are used.

You might find one of these references pointing to the use of TRIGGERS to be helpful:

Tuesday, March 27, 2012

archiving tables

Archiving Problem

Archiving Problem

Archiving Problem

Archiving is Not reducing DB Size

Archiving database for reporting

Archiving AS 2000 Database to a network backup server

Archiving AS 2000 Database to a network backup server

Archiving AS 2000 Database to a network backup server

Archiving a database

Archiving

Archiving

Archiving

Sunday, March 25, 2012

Archiving

Archiving

Archiving

Archive Strategy

Archive Stored Procedure

Archive Stored Procedure

Archive Stored Procedure

sql server tools

Blog Archive

About Me