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.
> > >
> > >
> >
> >
>

Application respond slowly

Dear All
My SQL Server is contains 2 databases. Both of them are used by
different client/server application and each application uses several
stored procedure to query and update its database. The problem is my
users reports that those two application respond slowly.
I used SQL Profile, and here the result.
EventClass______________________TestData
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1 =
SQL:BatchCompleted Update TableB Set Col2 = '23456..
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1=
What should I do to improve the performance?
- by using views to query data or modify the stored procedure so that
they select data into temporary tables.
Pls give me your advice
Thanks
Robert Lie
There's a boundless number of potential issues and bottlenecks. Far more
information is needed to help with this. First try to gather some
perfromance stats. Is the server busy? Is there blocking? What
perfromance do you get when running in query analyzer?
"robert lie" <robert.lie24@.gmail.com> wrote in message
news:ebnyU$RZFHA.2884@.tk2msftngp13.phx.gbl...
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by different
> client/server application and each application uses several stored
> procedure to query and update its database. The problem is my users
> reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 =
> SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=
>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that they
> select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie
|||As mentioned by Danny, there can be a lot of reason. Before you just jump to
Profiler, I'd investigate a bit more what the users means that the
applications respond slowly. It might help to know if it's something that
has happended all of a sudden or if performance has degraded slowly over
time. It will also be different things to look for if it's a new application
that just never has performed decent.
If performance has degraded slowly over time, I'd look at things like
missing statistic update, Index defragmentation, memory consumption,
Disksystem defragmention, logfile size (i.e. does it autogrow too often?),
disk space etc.
If performance has degraded all of a sudden, I'd more look for diskproblems,
disk space issues, server HW problems, CPU utilization etc. I'd then also
check what have been done to the server in terms of updates (Windows, SQL,
AntiVirus etc) to see if it could something that are consuming a lof of CPU.
If performance never has been decent with those applications I'd look at
more basic things like server configuration, RAID, disksystem, memory etc.
It could also be poorly designed applications, but if it's two applications
independent of each other, it's not very likely that they both suffer from
poor design.
I hope that some of the above can help you to troubleshoot the issues?
Regards
Steen
robert lie wrote:
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by
> different client/server application and each application uses several
> stored procedure to query and update its database. The problem is my
> users reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 =
> SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=
>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that
> they select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie

Application respond slowly

Dear All
My SQL Server is contains 2 databases. Both of them are used by
different client/server application and each application uses several
stored procedure to query and update its database. The problem is my
users reports that those two application respond slowly.
I used SQL Profile, and here the result.
EventClass______________________TestData
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1 = SQL:BatchCompleted Update TableB Set Col2 = '23456..
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1=
What should I do to improve the performance?
- by using views to query data or modify the stored procedure so that
they select data into temporary tables.
Pls give me your advice
Thanks
Robert LieThere's a boundless number of potential issues and bottlenecks. Far more
information is needed to help with this. First try to gather some
perfromance stats. Is the server busy? Is there blocking? What
perfromance do you get when running in query analyzer?
"robert lie" <robert.lie24@.gmail.com> wrote in message
news:ebnyU$RZFHA.2884@.tk2msftngp13.phx.gbl...
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by different
> client/server application and each application uses several stored
> procedure to query and update its database. The problem is my users
> reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 => SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that they
> select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie|||As mentioned by Danny, there can be a lot of reason. Before you just jump to
Profiler, I'd investigate a bit more what the users means that the
applications respond slowly. It might help to know if it's something that
has happended all of a sudden or if performance has degraded slowly over
time. It will also be different things to look for if it's a new application
that just never has performed decent.
If performance has degraded slowly over time, I'd look at things like
missing statistic update, Index defragmentation, memory consumption,
Disksystem defragmention, logfile size (i.e. does it autogrow too often?),
disk space etc.
If performance has degraded all of a sudden, I'd more look for diskproblems,
disk space issues, server HW problems, CPU utilization etc. I'd then also
check what have been done to the server in terms of updates (Windows, SQL,
AntiVirus etc) to see if it could something that are consuming a lof of CPU.
If performance never has been decent with those applications I'd look at
more basic things like server configuration, RAID, disksystem, memory etc.
It could also be poorly designed applications, but if it's two applications
independent of each other, it's not very likely that they both suffer from
poor design.
I hope that some of the above can help you to troubleshoot the issues?
Regards
Steen
robert lie wrote:
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by
> different client/server application and each application uses several
> stored procedure to query and update its database. The problem is my
> users reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 => SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that
> they select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie

Application respond slowly

Dear All
My SQL Server is contains 2 databases. Both of them are used by
different client/server application and each application uses several
stored procedure to query and update its database. The problem is my
users reports that those two application respond slowly.
I used SQL Profile, and here the result.
EventClass______________________TestData
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1 =
SQL:BatchCompleted Update TableB Set Col2 = '23456..
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1=
What should I do to improve the performance?
- by using views to query data or modify the stored procedure so that
they select data into temporary tables.
Pls give me your advice
Thanks
Robert LieThere's a boundless number of potential issues and bottlenecks. Far more
information is needed to help with this. First try to gather some
perfromance stats. Is the server busy? Is there blocking? What
perfromance do you get when running in query analyzer?
"robert lie" <robert.lie24@.gmail.com> wrote in message
news:ebnyU$RZFHA.2884@.tk2msftngp13.phx.gbl...
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by different
> client/server application and each application uses several stored
> procedure to query and update its database. The problem is my users
> reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 =
> SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=
>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that they
> select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie|||As mentioned by Danny, there can be a lot of reason. Before you just jump to
Profiler, I'd investigate a bit more what the users means that the
applications respond slowly. It might help to know if it's something that
has happended all of a sudden or if performance has degraded slowly over
time. It will also be different things to look for if it's a new application
that just never has performed decent.
If performance has degraded slowly over time, I'd look at things like
missing statistic update, Index defragmentation, memory consumption,
Disksystem defragmention, logfile size (i.e. does it autogrow too often?),
disk space etc.
If performance has degraded all of a sudden, I'd more look for diskproblems,
disk space issues, server HW problems, CPU utilization etc. I'd then also
check what have been done to the server in terms of updates (Windows, SQL,
AntiVirus etc) to see if it could something that are consuming a lof of CPU.
If performance never has been decent with those applications I'd look at
more basic things like server configuration, RAID, disksystem, memory etc.
It could also be poorly designed applications, but if it's two applications
independent of each other, it's not very likely that they both suffer from
poor design.
I hope that some of the above can help you to troubleshoot the issues?
Regards
Steen
robert lie wrote:
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by
> different client/server application and each application uses several
> stored procedure to query and update its database. The problem is my
> users reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 =
> SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=
>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that
> they select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie