From the standpoint of the government purchases component of GDP, state and local spending is a big deal - in 2009, state and local government purchases were $1790 billion, compared to $377 billion in nondefense federal government spending. To the extent that federal government is "big" it is because of military spending ($794 bn), transfers ($2139 bn), and interest payments ($272 bn).
Much of the recovery act was explicitly designed to reduce states' needs to cut back, but it didn't prove to be enough to completely offset state budget problems. Joshua Aizenman and Gurnain Kaur Pasricha have performed the useful exercise of calculating the "net stimulus" - the combined change in government purchases at the federal and state and local levels. They find that the net stimulus was only slightly positive. At Vox, they write:
[S]o far, the federal fiscal expenditure stimulus has mostly compensated for the negative state and local stimulus associated with the collapsing tax revenue and the limited borrowing capacity of the states. While this is a significant accomplishment, the net effect is that the consolidated fiscal expenditure stimulus is small relative to the sharp fall in private aggregate demand. Consumption plus gross investment expenditures of all levels of the US government were only $47.6 billion higher in 2009 than in 2008 whereas total expenditures (which include transfers) were $330 billion higher. Thus, the fiscal expenditure stimulus did not manage to provide a viable cushion for the negative stimulus associated with private sector’s declining demand. This observation is pertinent in explaining the anaemic reaction of the overall US economy to the alleged “big federal fiscal stimulus”.That provides a useful sense of perspective (and another bit of evidence for those who would argue the stimulus was too small). It is worth noting that, because they focus solely on government purchases, they are leaving out the effects of the tax cut components, which were about one-third of the recovery act.